Oct 22 2021 05:39 AM
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.
Oct 22 2021 08:20 AM
SolutionReplace
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)"
Oct 22 2021 09:13 PM
@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?
Oct 22 2021 08:20 AM
SolutionReplace
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)"