Help with Macro - copying formula to last line of data based on another column

kidwispa · #1 07-06-2015, 06:05 AM

Hi All,

I posted this thread in the Excel section by mistake instead of this section - can the mods please delete it from there?

I have the below macro that I recorded that copies some formulas I have stored on a sheet and pastes them onto another sheet (there is a reason behind this).

As each time I'll run this macro the number of rows it will need to copy to will vary, what do I need to change in my code in order for it to always copy down to the last row of data (instead of just row 20)? If it could be based on Column A (as there will always be a value in there when the row is populated.

Code:
Sub FormulaImport()
'
' FormulaImport Macro
'

'
Sheets("Formulas").Select

Range("BA2:BZ2").Select
Selection.Copy
Sheets("Master").Select
Range("BA2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("BA2:BZ20")
Range("BA2:BZ20").Select
End Sub
Thanks in advance for any help.

charlesdh · #2 07-12-2015, 05:01 PM

Hi,

It looks as if we dropped the ball here. Sorry we did not respond to your request.
I can see, however you did get an answer at

http://www.mrexcel.com/forum/excel-q...er-column.html

I left your code in. However, I remarked most of it.
Did not test.

Code:

Sub FormulaImport() '
' FormulaImport Macro
'
Dim Lastrow As Long


Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Formulas").Select
Range("BA2:BZ2").Copy
'Selection.Copy
Sheets("Master").Select
Range("BA2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("BA2:BZ2").AutoFill Destination:=Range("BA2:BA" & Lastrow)'''' Modified this line
'Range("BB2").AutoFill Destination:=Range("BB2:BB" & Lastrow)
'Range("BC2").AutoFill Destination:=Range("BC2:BC" & Lastrow)'
'Range("BD2").AutoFill Destination:=Range("BD2:BD" & Lastrow)
'Range("BE2").AutoFill Destination:=Range("BE2:BE" & Lastrow)
'Range("BF2").AutoFill Destination:=Range("BF2:BF" & Lastrow)
'Range("BG2").AutoFill Destination:=Range("BG2:BG" & Lastrow)
'Range("BH2").AutoFill Destination:=Range("BH2:BH" & Lastrow)
'Range("BI2").AutoFill Destination:=Range("BI2:BI" & Lastrow)
'Range("BJ2").AutoFill Destination:=Range("BJ2:BJ" & Lastrow)
'Range("BK2").AutoFill Destination:=Range("BK2:BK" & Lastrow)
'Range("BL2").AutoFill Destination:=Range("BL2:BL" & Lastrow)
'Range("BM2").AutoFill Destination:=Range("BM2:BM" & Lastrow)
'Range("BN2").AutoFill Destination:=Range("BN2:BN" & Lastrow)
'Range("BO2").AutoFill Destination:=Range("BO2:BO" & Lastrow)
'Range("BP2").AutoFill Destination:=Range("BP2:BP" & Lastrow)
'Range("BQ2").AutoFill Destination:=Range("BQ2:BQ" & Lastrow)
'Range("BR2").AutoFill Destination:=Range("BR2:BR" & Lastrow)
'Range("BS2").AutoFill Destination:=Range("BS2:BS" & Lastrow)
'Range("BT2").AutoFill Destination:=Range("BT2:BT" & Lastrow)
'Range("BU2").AutoFill Destination:=Range("BU2:BU" & Lastrow)
'Range("BV2").AutoFill Destination:=Range("BV2:BV" & Lastrow)
'Range("BW2").AutoFill Destination:=Range("BW2:BW" & Lastrow)
'Range("BX2").AutoFill Destination:=Range("BX2:BX" & Lastrow)
'Range("BY2").AutoFill Destination:=Range("BY2:BY" & Lastrow)
'Range("BZ2").AutoFill Destination:=Range("BZ2:BZ" & Lastrow)


Range("A2").Select


End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel VBA Macro - Deleting Specific Data based on criteria	MD011	Excel Programming	3	12-10-2014 02:15 AM
Macro for Column to Rows Data	brunssl2	Excel Programming	3	04-28-2014 07:07 AM
formula to work from data column	masterdineen	Excel	16	08-09-2012 07:52 AM
Creating formula based on if data is correct in cell	MattMurdock	Excel	1	08-06-2012 03:11 AM