hhelhenawy
New Member
- Joined
- Jul 1, 2021
- Messages
- 2
- Office Version
- 365
- 2016
- Platform
- Windows
I have this Code
I am trying to copy data from oneworkbook and add it to another which works fine
The other part I am tryin to vlookup data the lookup value in one workbook and the vlookup table in another workbook and results should be added in the third workbook, I have this code but I get the debug error for the vlookup worksheet function.
Any help to fix it will be great and mostly the whole project depending on this
Sub Copy_Paste_Below_Last_Cell()
'Find the last used row in both sheets and copy and paste data below existing data.
I am trying to copy data from oneworkbook and add it to another which works fine
The other part I am tryin to vlookup data the lookup value in one workbook and the vlookup table in another workbook and results should be added in the third workbook, I have this code but I get the debug error for the vlookup worksheet function.
Any help to fix it will be great and mostly the whole project depending on this
Sub Copy_Paste_Below_Last_Cell()
'Find the last used row in both sheets and copy and paste data below existing data.
VBA Code:
'Sub OpenWorkbook()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim wsMapp As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim lLookupLastRow As Long
'Set variables for copy , vlookup and destination sheets
Set wsCopy = Workbooks("A1. Syndicate 623 QMA_20190930_5_0623 with old version table.xlsx").Worksheets("360")
Set wsDest = Workbooks("Reports.xlsm").Worksheets("Old")
Set wsMapp = Workbooks("QMA new format mapping to old.xlsx").Worksheets("360")
'1. Find last used row in the copy range based on data in column D
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "D").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column J
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "J").End(xlUp).Offset(1).Row
'3. Copy & Paste Data
wsCopy.Range("D15:D" & lCopyLastRow).Copy
wsDest.Range("J" & lDestLastRow).PasteSpecial Paste:=xlPasteValues
'vlookup
'1. Find Last used row in the lookup range based on Data in Column B
lLookupLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
'2. Select Vlookup Start Row
lLookupLastRow = wsDest.Cells(wsDest.Rows.Count, "I").End(xlUp).Offset(1).Row
'3. Vlookup
Dim Table1 As Range
Dim Table2 As Range
Dim cl As Range
Set Table1 = wsCopy.Range("B15:B" & lLookupLastRow)
Set Table2 = wsMapp.Range("C15:D37")
For Each cl In Table1
wsDest.Range("I" & lLookupLastRow) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
Next cl
MsgBox "Done"
'Optional - Select the destination sheet
wsDest.Activate
'Sub CloseWorkbook()
End Sub
Last edited by a moderator: