copy pasting multiple columns from multiple workbooks into one master workbook

mogss_04

Board Regular
Joined
May 9, 2019
Messages
57
Hi can you let me know how to code the below,

I have 3 files named Belarus, Belarus2 and Belarus3 and i need to paste the the below from each of the files into master file.Would it be possible to execute all at once. I have all the files saved in the folder in the path "C:\Users\Priyanka Singh\Desktop\VBA code"

Belarus file to master file
1. "Country" column from Belarus to "Country" in masterfile
2. "Material " column from Belarus to "ITEM_CODE" in master file
3. "Material Name" column from Belarus to "ITEM_DESCR" in master file
4. "Batch" column from Belarus to "LOT_NO" in master file
5. "Manufacturing Date" column from Belarus to "MFG_DATE"
6. "Batch Expiry Date" column from Belarus to "EXP_DATE" in master file
7. "Total Qty column" from Belarus to "QUANTITY" in master file

Belarus2 file to master file
1. "Country" column from Belarus2 to "Country" in masterfile
2. "HANA Code" column from Belarus2 to "ITEM_CODE" in masterfile
3. "Product Name" column from Belarus2 to "ITEM_DESCR" in masterfile
4. "Total Stock Qty" column from Belarus2 to "QUANTITY" in masterfile

Belarus3 file to master file
1. "Country" column from Belarus3 to "Country" in masterfile
2. "Material Code " column from Belarus3 to "ITEM_CODE" in master file
3. "Material " column from Belarus3 to "ITEM_DESCR" in master file
4. "Usage " column from Belarus3 to "Inventory Flag" in master file
5. "Batch Creation Date" column from Belarus3 to "MFG_DATE"
6. "Batch Expiry Date" column from Belarus3 to "EXP_DATE" in master file
7. "Qty Sales Unit (derived from base unit & product description)" column from Belarus3 to "QUANTITY" in master file

Thank you.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Are the 3 files the only files in folder "VBA code"? What is the extension of the 3 files (xlsx,xlsm)? Rather than using the column headers, could you use column letters to explain which columns you want to copy and to which columns you want to paste, for example: column A from Belarus to column B in Masterfile. What is the name of the destination sheet in the Masterfile?
 
Upvote 0
Hi mumps,

the source sheets is "base" for all 3 worksheets and the destination sheet name is "Base inv data". the extension of all 3 files is xlsx. Below are the column letters in fron of column names. Let me know fora anything else


Belarus file to master file
1. "Country A" column from Belarus to "Country F" in masterfile
2. "Material C " column from Belarus to "ITEM_CODE C" in master file
3. "Material Name D" column from Belarus to "ITEM_DESCR E" in master file
4. "Batch E" column from Belarus to "LOT_NO G" in master file
5. "Manufacturing Date F" column from Belarus to "MFG_DATE J"
6. "Batch Expiry Date G" column from Belarus to "EXP_DATE K" in master file
7. "Total Qty column P" from Belarus to "QUANTITY L" in master file


Belarus2 file to master file
1. "Country B" column from Belarus2 to "Country F" in masterfile
2. "HANA Code D" column from Belarus2 to "ITEM_CODE C" in masterfile
3. "Product Name E" column from Belarus2 to "ITEM_DESCR E" in masterfile
4. "Total Stock Qty HR" column from Belarus2 to "QUANTITY L" in masterfile


Belarus3 file to master file
1. "Country F" column from Belaru sjs3 to "Country F" in masterfile
2. "Material Code B" column from Belarus3 to "ITEM_CODE C" in master file
3. "Material C " column from Belarus3 to "ITEM_DESCR E" in master file
4. "Usage I " column from Belarus3 to "Inventory Flag O" in master file
5. "Batch Creation Date L" column from Belarus3 to "MFG_DATE J"
6. "Batch Expiry Date M" column from Belarus3 to "EXP_DATE K" in master file
7. "Qty Sales Unit (derived from base unit & product description) R" column from Belarus3 to "QUANTITY L" in master file

Thank you.
 
Upvote 0
Try:
Code:
Sub CopyRange()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWB As Workbook, LastRow1 As Long, LastRow2 As Long
    Set desWS = ThisWorkbook.Sheets("Base inv data")
    LastRow1 = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Set srcWB = Workbooks.Open("C:\Users\Priyanka Singh\Desktop\VBA code\Belarus.xlsx")
    With Sheets("base")
        LastRow2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A2:A" & LastRow2).Copy desWS.Cells(LastRow1, "F")
        .Range("C2:C" & LastRow2).Copy desWS.Cells(LastRow1, "C")
        .Range("D2:D" & LastRow2).Copy desWS.Cells(LastRow1, "E")
        .Range("E2:E" & LastRow2).Copy desWS.Cells(LastRow1, "G")
        .Range("F2:F" & LastRow2).Copy desWS.Cells(LastRow1, "J")
        .Range("G2:G" & LastRow2).Copy desWS.Cells(LastRow1, "K")
        .Range("P2:P" & LastRow2).Copy desWS.Cells(LastRow1, "L")
    End With
    srcWB.Close savechanges:=False
    LastRow1 = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Set srcWB = Workbooks.Open("C:\Users\Priyanka Singh\Desktop\VBA code\Belarus2.xlsx")
    With Sheets("base")
        LastRow2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("B2:B" & LastRow2).Copy desWS.Cells(LastRow1, "F")
        .Range("D2:D" & LastRow2).Copy desWS.Cells(LastRow1, "C")
        .Range("E2:E" & LastRow2).Copy desWS.Cells(LastRow1, "E")
        .Range("HR2:HR" & LastRow2).Copy desWS.Cells(LastRow1, "L")
    End With
    srcWB.Close savechanges:=False
    LastRow1 = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Set srcWB = Workbooks.Open("C:\Users\Priyanka Singh\Desktop\VBA code\Belarus3.xlsx")
    With Sheets("base")
        LastRow2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("F2:F" & LastRow2).Copy desWS.Cells(LastRow1, "F")
        .Range("B2:B" & LastRow2).Copy desWS.Cells(LastRow1, "C")
        .Range("C2:C" & LastRow2).Copy desWS.Cells(LastRow1, "E")
        .Range("I2:I" & LastRow2).Copy desWS.Cells(LastRow1, "O")
        .Range("L2:L" & LastRow2).Copy desWS.Cells(LastRow1, "J")
        .Range("M2:M" & LastRow2).Copy desWS.Cells(LastRow1, "K")
        .Range("R2:R" & LastRow2).Copy desWS.Cells(LastRow1, "L")
    End With
    srcWB.Close savechanges:=False
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Which line of code is highlighted when you click "Debug"?
 
Upvote 0
It is not showing me the debug msg. The belarus file opens up. I did step into and it stops at "With Sheets("base")" line
 
Upvote 0
I pasted the code in "ThisWorkbook" under master file project. Should i be creating a module and then inserting the code in that. Just wanted to make sure if it is okay what I did.
 
Upvote 0
The macro should be placed in a standard module not in "ThisWorkbook". Do you have a sheet named "base" in the Belarus files?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top