SOLVED

Use VBA to Autofill a Row until the end of the number of data in another row

Copper Contributor

Hi,

 

I am trying to get data from another document and save it another one. For example, getting data from Book 2 and saving it to Book 1.  The issue is, this book 2 has 23,328 rows and now I have the same in Book 1. But when I use a new Book which is Book 3, it has above 30,000 rows. If I use the same Macro, it only fills up till 23,328 and remaining are blank. How do I use the same Macro to fill up the remaining rows?

 

Here is the Macro thing:

 

Sub Macro_Hardlines()
'
' Macro_Hardlines Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Windows("OIH_Rec_Vendor_Funding_Deals_1_HARDLINES.xlsx").Activate
Sheets("Vendor_Funding_ASINs_PV").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("his_in_pro").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("his_in_pro")
.PivotItems("Y").Visible = True
End With
Range("A14").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
ActiveSheet.Paste
Range("R2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],[OIH_Rec_Vendor_Funding_Deals_1_HARDLINES.xlsx]Deal_ASIN_Details!C11:C35,25,0)"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R23328")
Range("R2:R23328").Select
End Sub

 

Thank you. 

2 Replies
best response confirmed by KannanKumar (Copper Contributor)
Solution

@KannanKumar 

Replace

Range("R2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],[OIH_Rec_Vendor_Funding_Deals_1_HARDLINES.xlsx]Deal_ASIN_Details!C11:C35,25,0)"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R23328")
Range("R2:R23328").Select

with

    Dim m As Long
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("R2:R" & m).FormulaR1C1 = _
        "=VLOOKUP(RC[-13],[OIH_Rec_Vendor_Funding_Deals_1_HARDLINES.xlsx]Deal_ASIN_Details!C11:C35,25,0)"

@Hans Vogelaar Awesome, thanks for your quick response - this works perfectly!
If you could help me with another one, I will really appreciate it. After using the Macro, the final row in each sheets should be deleted automatically. Is there a way to do that? From the image I have provided, you can see the data is in R1738. But this row number will vary in all sheets however Data in the column E and Column R will remain the same for this row. How can I delete this entire row using Macro?

1 best response

Accepted Solutions
best response confirmed by KannanKumar (Copper Contributor)
Solution

@KannanKumar 

Replace

Range("R2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],[OIH_Rec_Vendor_Funding_Deals_1_HARDLINES.xlsx]Deal_ASIN_Details!C11:C35,25,0)"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R23328")
Range("R2:R23328").Select

with

    Dim m As Long
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("R2:R" & m).FormulaR1C1 = _
        "=VLOOKUP(RC[-13],[OIH_Rec_Vendor_Funding_Deals_1_HARDLINES.xlsx]Deal_ASIN_Details!C11:C35,25,0)"

View solution in original post