Copy cell only as far as the previous column cells go down in Macro

[Peter Davison]

Hi,
I have a cell F1 which I want to copy in a macro as far down the column as data goes in Column E.
The reason I can't leave a static formula in the cell is because it relates to a chart and if there is a formula in the cells lower than data in column E it will extend the width of the graph.
As I am new to VBA in Excel I'm not sure how to do this. Can you help?
Thanks

 

[Peter Davison]

Does this routine have to be within its own start sub and end sub?

 

[Fluff]

My suggestion was to replace this line of code

 Range("G2:G" & lr) = Range("G2").Formula

 

[Rick Rothstein]

Post a copy of your workbook to a well-known free file sharing website (my recommendation is DropBox) so that we can see why the posted code is not working for your data.

 

[Peter Davison]

Yes I did replace it with the below, but that did not work it just left the G2 cell with the formula and the rest empty
Range("G2:G" & lr).filldown
I've just tried it again and it definitely does fill the cells.
Does it need to be within its own sub starts and end?

 

[Peter Davison]

does not fill (not does fill)

 

[Fluff]

Can you please post the code you are using.

 

[Peter Davison]

Workbooks("filename").Worksheets("rep writer variance report").Activate
Dim lr As Long: lr = Range("F" & Rows.Count).End(xlUp).Row

Worksheets("rep writer variance report").Range("G4:g" & lr) = Range("G4").Formula

 

[Fluff]

That doesn't have the code change I suggested.

 

[Peter Davison]

I did change it as per below just changed it back when it didn't work.

Workbooks("filename").Worksheets("rep writer variance report").Activate
Dim lr As Long: lr = Range("F" & Rows.Count).End(xlUp).Row

Range("G2:G" & lr).filldown

and I also tried it with the sheet name in front of "Range"

 

[Fluff]

If you add a message box like

Dim lr As Long: lr = Range("F" & Rows.Count).End(xlUp).Row
MsgBox lr
Range("G2:G" & lr).filldown

What does it say?