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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,912
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?
 

mogss_04

Board Regular
Joined
May 9, 2019
Messages
57
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.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,912
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
 

mogss_04

Board Regular
Joined
May 9, 2019
Messages
57
hi Mumps,

I am getting an error "Run-time error'9' subscript out of range.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,912
Which line of code is highlighted when you click "Debug"?
 

mogss_04

Board Regular
Joined
May 9, 2019
Messages
57
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
 

mogss_04

Board Regular
Joined
May 9, 2019
Messages
57
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.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,912
The macro should be placed in a standard module not in "ThisWorkbook". Do you have a sheet named "base" in the Belarus files?
 

Watch MrExcel Video

Forum statistics

Threads
1,096,312
Messages
5,449,617
Members
405,573
Latest member
Masimo85

This Week's Hot Topics

Top