Using .AutoFill with a CELLS() reference

  • Thread starter Thread starter Greg Glynn
  • Start date Start date
G

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
 
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)
 
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
 
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
 
Here's the fix:

Dim MyRange as Range
Set MyRange=.Range(Cells(7,7),Cells(7,14))
..Range("G7").AutoFill Destination:=MyRange, Type:=xlFillDefault

Apparently the Destination:= keyword needs to receive a Range Object,
and .Range(Cells(7,7),Cells(7,14)) doesn't cut the mustard.
 
You also have trouble waiting to happen here:

With Worksheets("MyQuery")
.Range(Cells(5, 1), Cells(65000, 255)).Clear
.Range(Cells(4, 6), Cells(4, 255)).Clear

Those cells() are unqualified. If myQuery isn't the activesheet, it'll fail.

With Worksheets("MyQuery")
.Range(.Cells(5, 1), .Cells(65000, 255)).Clear
.Range(.Cells(4, 6), .Cells(4, 255)).Clear

Same here:

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

or
.Range("F4").AutoFill Destination:=.Range("F4", .Cells(4, 12)), _
Type:=xlFillDefault
 
Well spotted. I'll fix that up. Thanks for the proof-read.

(These things happen when you teach yourself) :-)
 
Back
Top