VBA to copy formula to last 2 columns after the Blank Column

[TheHack22]

Hi All,

I have this basic VBA to copy and paste formulas to lastrow when the data is populated. The template has now changed, even though this seems simple I can't figure out how to do this.

How do I copy the formula in cell (L2 to AG2) all the way do to the Last used row? Here is my current code that needed to be changed.

Sub PIT_9_US()
Sheets("PIT 9 - US").Select
Range("L2").Select
Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste

End Sub

Imran

 

[Joe4]

How do I copy the formula in cell (L2 to AG2) all the way do to the Last used row?

What column can we look at to determine the last user row?
What column will always be populated?

 

[Joe4]

If we can use column K, this will do what you want:

Sub PIT_9_US()

    Dim lr As Long
    
    Sheets("PIT 9 - US").Select
    
'   Find last row with data in column K
    lr = Cells(Rows.Count, "K").End(xlUp).Row
    
'   Copy formulas
    If lr > 2 Then Range("L2:AG2").Copy Range("L3:L" & lr)
    
End Sub

(Note that having all those "Selects" is unnecessary and actually slows your code down. It is often the product of the Macro Recorder, and can usually be cleaned up a bit.)

 

[TheHack22]

What column can we look at to determine the last user row?
What column will always be populated?

@Joe4

Thanks for your prompt response.

a) What column can we look at to determine the last user row?
Column J

b) What column will always be populated?
Column L::X & Z:AA
(Excludes Col Y)

Imran

 

[TheHack22]

ImranIf we can use column K, this will do what you want:

Sub PIT_9_US()

    Dim lr As Long
   
    Sheets("PIT 9 - US").Select
   
'   Find last row with data in column K
    lr = Cells(Rows.Count, "K").End(xlUp).Row
   
'   Copy formulas
    If lr > 2 Then Range("L2:AG2").Copy Range("L3:L" & lr)
   
End Sub

(Note that having all those "Selects" is unnecessary and actually slows your code down. It is often the product of the Macro Recorder, and can usually be cleaned up a bit.)

@Joe4

Thanks very much. I think I can work with this, will modify it, and provide some feedback.
Column K works too.

Imran

 

[TheHack22]

If we can use column K, this will do what you want:

Sub PIT_9_US()

    Dim lr As Long
   
    Sheets("PIT 9 - US").Select
   
'   Find last row with data in column K
    lr = Cells(Rows.Count, "K").End(xlUp).Row
   
'   Copy formulas
    If lr > 2 Then Range("L2:AG2").Copy Range("L3:L" & lr)
   
End Sub

(Note that having all those "Selects" is unnecessary and actually slows your code down. It is often the product of the Macro Recorder, and can usually be cleaned up a bit.)

Joe,

Thanks so much. This works perfectly. You're the best.

Imran

 

[Joe4]

You are welcome.
Glad I was able to help!