PDA

View Full Version : Solved: macro to copy data - down other columns



Pete
12-08-2009, 02:09 AM
Hi

Need to change the current macro so that is populates the formula down columns B:F based (staring at row 6) on column A6 until the las cell with data....


Sub Macro4()
Workbooks.Open Filename:="D:\Users\LKassai\Desktop\VOCH\SA38.xlsm"
Windows("Margin File.xlsm").Activate
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],SA38.xlsm!C1:C8,2,0)"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],SA38.xlsm!C1:C8,5,0)"
Range("D6").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],SA38.xlsm!C1:C8,6,0)"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],SA38.xlsm!C1:C8,7,0)"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],SA38.xlsm!C1:C8,8,0)"
Range("F7").Select

End Sub

xld
12-08-2009, 02:33 AM
Sub Macro4()
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Workbooks.Open Filename:="D:\Users\LKassai\Desktop\VOCH\SA38.xlsm"

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B6").Resize(Lastrow - 5).FormulaR1C1 = "=VLOOKUP(RC[-1],SA38.xlsm!C1:C8,2,0)"
.Range("C6").Resize(Lastrow - 5).FormulaR1C1 = "=VLOOKUP(RC[-2],SA38.xlsm!C1:C8,5,0)"
.Range("D6").Resize(Lastrow - 5).FormulaR1C1 = "=VLOOKUP(RC[-3],SA38.xlsm!C1:C8,6,0)"
.Range("E6").Resize(Lastrow - 5).FormulaR1C1 = "=VLOOKUP(RC[-4],SA38.xlsm!C1:C8,7,0)"
.Range("F6").Resize(Lastrow - 5).FormulaR1C1 = "=VLOOKUP(RC[-5],SA38.xlsm!C1:C8,8,0)"
End With

End Sub

Pete
12-08-2009, 04:16 AM
thanks xld wroks prefectly

Pete
12-08-2009, 04:17 AM
hi

Need to finish this macro so that it populates the results in columns M:N based on column A data last cell


Sub Vlookup_KE_24()

Application.ScreenUpdating = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],'KE24'!C1:C13,7,0)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],'KE24'!C1:C13,13,0)"

Application.ScreenUpdating = True
End Sub

Pete
12-08-2009, 04:18 AM
hi

Need to finish this macro so that it populates the results in columns M:N based on column A data last cell




VBA:
Sub Vlookup_KE_24() Application.ScreenUpdating = False ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],'KE24'!C1:C13,7,0)" ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],'KE24'!C1:C13,13,0)" Application.ScreenUpdating = True End Sub

xld
12-08-2009, 04:29 AM
Same as before, just change the ranges.