Using .AutoFill with a CELLS() reference

[Greg Glynn]

Hi,

This works:

Worksheets("Sheet1").Range("G7").AutoFill Destination:=Range("G7:K7"),
Type:=xlFillDefault

but this doesn't ...

Worksheets("Sheet1").Range("G7").AutoFill
Destination:=Range(CELLS(7,7),CELLS(7,13)), Type:=xlFillDefault

Can anyone suggest a way to autofill some column headings for numeric
number of columns?

Ideally, I'd like to do something like this:

Dim MyColumns as Integer
MyColumns = 8
Worksheets("Sheet1").Range("G7").AutoFill
Destination:=Range(CELLS(7,7),CELLS(7,7 + MyColumns)),
Type:=xlFillDefault

Any help would be appreciated.

Greg

 

[Bob Phillips]

It works fine if Sheet1 is the activesheet. otherwsie try

With Worksheets("Sheet1")
.Range("G7").AutoFill Destination:=.Range(.CELLS(7,7),.CELLS(7,13)),
Type:=xlFillDefault
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Greg,

All of what you have posted works OK, what's the issue?

Mike

 

[Mike Fogleman]

I've got good news and bad news. The good news is all 3 of your codes works
fine for me (XL2K, WinXP). The bad news is it doesn't work for you. So there
must be some other underlying issue besides the code.

Mike F

 

[Greg Glynn]

Hi Bob,

I changed my code from "Thisworksheet.sheets.range( ...." to the "with/
end with" structure as you suggested. I'm getting "autofill method of
range class failed" on the last line (before the 'end with'). Maybe I
need to Activate the sheet?

With Worksheets("MyQuery")

.Range(Cells(5, 1), Cells(65000, 255)).Clear
.Range(Cells(4, 6), Cells(4, 255)).Clear
.Range("A6").Value = "Agency"
.Range("B6").Value = "Server"
.Range("C6").Value = "Policy"
.Range("D6").Value = "Description"
.Rows("6:6").Font.Bold = True

ReportDays = ReportCriteria.PickFinishDate -
ReportCriteria.PickStartDate

'Write the Dates for the Grid Column Headings
ReDim DateArray(ReportDays + 1)

For i = 0 To ReportDays
.Cells(6, 6 + i) = ReportCriteria.PickStartDate + i
.Cells(6, 6 + i).NumberFormat = "dd mmm"
.Cells(6, 6 + i).HorizontalAlignment = xlCenter

.Cells(5, 6 + i) = ReportCriteria.PickStartDate + i
.Cells(5, 6 + i).NumberFormat = "ddd"
.Cells(5, 6 + i).HorizontalAlignment = xlCenter
DateArray(i + 1) = FormatDateTime(ReportCriteria.PickStartDate + i,
vbShortDate)
Next i

.Range("F4").AutoFill Destination:=.Range(Cells(4, 5), Cells(4,
12)), Type:=xlFillDefault
End With

 

[Greg Glynn]

Well spotted. I'll fix that up. Thanks for the proof-read.

(These things happen when you teach yourself)

 

[Dave Peterson]

I think you're solution needs to have those cells() qualified, too.