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

Imran_IsshackNY

Board Regular
Joined
Feb 3, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
1632774101035.png
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,057
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,057
Office Version
  1. 365
Platform
  1. Windows
If we can use column K, this will do what you want:
VBA Code:
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.)
 
Solution

Imran_IsshackNY

Board Regular
Joined
Feb 3, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
 

Imran_IsshackNY

Board Regular
Joined
Feb 3, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

ImranIf we can use column K, this will do what you want:
VBA Code:
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
 

Imran_IsshackNY

Board Regular
Joined
Feb 3, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
If we can use column K, this will do what you want:
VBA Code:
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
 

Forum statistics

Threads
1,147,635
Messages
5,742,245
Members
423,717
Latest member
rubthenut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top