1

I am attempting to copy formulas from the last row in columns "O" to "V" down to the last used row in column "A" but I am having difficulty. This is a bit more advanced than I am used to as I can manage to get data from cell "O2" to copy down to the last row but the formula will always been in a different row and it is a large set of data I am adding to and therefore I don't wish to recalculate the full page each day.

Extract of Code

 LastRow = ActiveSheet.UsedRange.Rows.Count
LR1 = Range("O" & Rows.Count).End(xlUp).Row
Range("O" & LR1).Select
Selection.Copy
LastRow = ActiveSheet.UsedRange.Rows.Count
LR2 = Range("A" & Rows.Count).End(xlUp).Row
Range("O" & LR1).AutoFill Destination:=Range("O" & LR2), Type:=xlFillDefault

It looks to be the last line that is causing the issues but it could be incorrect. To give you a bit more of what I have completed to this point- I have copied 4385 lines of data into columns "A:N" and now need to copy the formula that was in the 1st row I pasted into down to the end of my data set now + 1 extra row.

I am then wanting to select the formulas that I have copied down and paste special all but the last row of formula in columns "O to V"

Is it possible to do this?

Steven Craig
  • 31
  • 1
  • 8

1 Answers1

1

You should be able to avoid actually selecting anything with this.

Dim LR1 As Long, LR2 As Long
LR1 = Range("O" & Rows.Count).End(xlUp).Row
LR2 = Range("A" & Rows.Count).End(xlUp).Row
Range("O" & LR1 & ":V" & LR2).FillDown

*Addendum: * The correct command was FillDown, not Autofill.

  • 1
    + 1 For getting the question right :) I misunderstood the question. Just a small suggestion.. Change your last line to `Range("O" & LR1 & ":V" & LR1).AutoFill Destination:=Range("O" & LR2 & ":V" & LR2), Type:=xlFillDefault` – Siddharth Rout Oct 30 '14 at 14:25
  • This code is coming back with an error "Compile error Argument not optional" with the .AutoFill highlighted. I have entered this just as above, am I missing something? – Steven Craig Oct 30 '14 at 14:28
  • @SiddharthRout I have added your code but I am still getting an Autofill method error. can you help further? – Steven Craig Oct 30 '14 at 14:33
  • @Steven Craig = My apologies. I should have checked that code but I didn't and it bit me in my southern regions. The command I was thinking of was `.FillDown`, not the `.AutoFill` that you had originally implemented. –  Oct 30 '14 at 14:35
  • 1
    @SiddharthRout - Thanks for catching that. I've opted for the more succinct `.FillDown` (which I was thinking of in the first place). In any event, nice catch. –  Oct 30 '14 at 14:36
  • @SiddharthRout Thank you both for the help this now works perfectly – Steven Craig Oct 30 '14 at 16:04