copy pasting multiple columns from multiple workbooks into one master workbook

[mogss_04]

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.

 

[mumps]

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]

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]

Try:

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]

hi Mumps,

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

 

[mumps]

Which line of code is highlighted when you click "Debug"?

 

[mogss_04]

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]

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]

The macro should be placed in a standard module not in "ThisWorkbook". Do you have a sheet named "base" in the Belarus files?

 

[mogss_04]

yes i have a sheet named "base" in all Belarus files.