Formula Macro

S

Secret Squirrel

I have a formula in cell C2. I want to create a macro in VBA to fill down
that formula into cells C3:C100. How would I write that code?
 
D

Dave Peterson

range("C3:c100").formula = range("C2").formula

or
range("C2").autofill _
destination:=range("C2:C100")
 
S

Secret Squirrel

Hi Again!

Thanks for the response. After I posted this I was tinkering around with it
and I got it to work using this code:

Worksheet("Sheet1").Range("C2:C100").FillDown

I assume it does the same thing as what you posted?
 
D

Dave Peterson

In this case, yep.

But .autofill has lots more options that you may find exciting <bg> someday.
 
S

Secret Squirrel

Ok last question... I've compiled this macro from a couple of my posts
tonight but when I fire the macro it doesn't do the filldown unless I fire it
twice in a row. Any idea why that might be? The "RefreshAll" is to refresh my
query I have in my workbook.

Public Sub CopyTwo()

ActiveWorkbook.RefreshAll
Worksheets("Sheet1").Range("C2:C100").FillDown
Worksheets("Sheet1").Range("A1:C100").Copy
With Worksheets("Sheet2")
..Range("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.Goto .Range("A1"), Scroll:=True

End With

With Worksheets("Sheet1")
Application.Goto .Range("A1"), Scroll:=True

End With

Application.CutCopyMode = False

End Sub
 
B

Bob Phillips

I don't have a query, but the rest works first time for me.

--
HTH

Bob Phillips

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

Secret Squirrel said:
Ok last question... I've compiled this macro from a couple of my posts
tonight but when I fire the macro it doesn't do the filldown unless I fire it
twice in a row. Any idea why that might be? The "RefreshAll" is to refresh my
query I have in my workbook.

Public Sub CopyTwo()

ActiveWorkbook.RefreshAll
Worksheets("Sheet1").Range("C2:C100").FillDown
Worksheets("Sheet1").Range("A1:C100").Copy
With Worksheets("Sheet2")
.Range("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.Goto .Range("A1"), Scroll:=True

End With

With Worksheets("Sheet1")
Application.Goto .Range("A1"), Scroll:=True

End With

Application.CutCopyMode = False

End Sub
Click to expand...
 
D

Dave Peterson

Maybe...

Rightclick on your data|query range and select Data Range Properties.
Try changing the "enable background refresh" to off.

In code, something like:
worksheets("Sheet1").QueryTables(1).Refresh Backgroundquery:=false

If c2 is part of that querytable range, that is.

Secret said:
Ok last question... I've compiled this macro from a couple of my posts
tonight but when I fire the macro it doesn't do the filldown unless I fire it
twice in a row. Any idea why that might be? The "RefreshAll" is to refresh my
query I have in my workbook.

Public Sub CopyTwo()

ActiveWorkbook.RefreshAll
Worksheets("Sheet1").Range("C2:C100").FillDown
Worksheets("Sheet1").Range("A1:C100").Copy
With Worksheets("Sheet2")
.Range("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.Goto .Range("A1"), Scroll:=True

End With

With Worksheets("Sheet1")
Application.Goto .Range("A1"), Scroll:=True

End With

Application.CutCopyMode = False

End Sub
Click to expand...
 
Top