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]

Try this macro to take care of the new criterium:

Sub copyColumns()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWB As Workbook, srcWS As Worksheet, x As Long, i As Long, LastRow As Long, LastRow2 As Long, rDate As Range
    Set desWS = ThisWorkbook.Sheets("Base inv data")
    Const strPath As String = "C:\Users\Priyanka Singh\Desktop\VBA code1\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        If srcWB.Name = "Belarus 3.xlsx" Then
            Set srcWS = Sheets("base")
            LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            With desWS.Range("C:C,E:E,F:F,J:J,K:K")
                For i = 1 To .Areas.Count
                    LastRow2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                    x = .Areas(i).Column
                    Set Header = srcWS.Rows(1).Find(.Areas(i).Cells(1), LookIn:=xlValues, lookat:=xlWhole)
                    If Not Header Is Nothing Then
                        srcWS.Range(srcWS.Cells(2, Header.Column), srcWS.Cells(LastRow, Header.Column)).Copy desWS.Cells(LastRow2, x)
                    End If
                Next i
            End With
            With srcWS
                .Range("B1:N" & LastRow).AutoFilter Field:=8, Criteria1:="=Unrestricted Use", Operator:=xlOr, Criteria2:="=Unrestricted-Use Mat"
                For Each rDate In .Range("M2:M" & LastRow).SpecialCells(xlCellTypeVisible)
                    If rDate.Value >= DateSerial(Year(Date) + 1, Month(Date) + 1, 1) Then
                        rDate.Offset(0, 1) = "Usable (>12)"
                    ElseIf rDate.Value >= DateSerial(Year(Date), Month(Date) + 7, 1) And rDate.Value < DateSerial(Year(Date) + 1, Month(Date) + 1, 1) Then
                        rDate.Offset(0, 1) = "Usable (7-12)"
                    ElseIf rDate.Value >= DateSerial(Year(Date), Month(Date), 1) And rDate.Value < DateSerial(Year(Date), Month(Date) + 7, 1) Then
                        rDate.Offset(0, 1) = "Near expiry"
                    ElseIf rDate.Value < DateSerial(Year(Date), Month(Date), 1) Then
                        rDate.Offset(0, 1) = "Expired"
                    End If
                Next rDate
                .Range("B1:N" & LastRow).AutoFilter Field:=8, Criteria1:="Blocked Stock", Operator:=xlOr, Criteria2:="Valuated Goods Receipt Blocked Stock"
                .Range("N2:N" & LastRow).SpecialCells(xlCellTypeVisible) = "Blocked"
                .Range("B1:N" & LastRow).AutoFilter Field:=8, Criteria1:="Transit", Operator:=xlOr, Criteria2:="Intransit"
                .Range("N2:N" & LastRow).SpecialCells(xlCellTypeVisible) = "Transit"
                .Range("B1:N" & LastRow).AutoFilter Field:=8, Criteria1:="Quality inspection"
                .Range("N2:N" & LastRow).SpecialCells(xlCellTypeVisible) = "Quality inspection"
                .Range("B1:N" & LastRow).AutoFilter Field:=8, Criteria1:="Restricted-Use"
                .Range("N2:N" & LastRow).SpecialCells(xlCellTypeVisible) = "Restricted"
                .Range("B1").AutoFilter
                .Range("B1:N" & LastRow).AutoFilter Field:=12, Criteria1:="=", Operator:=xlOr, Criteria2:="=#"
                For Each rDate In .Range("M2:M" & LastRow).SpecialCells(xlCellTypeVisible)
                    If (rDate = "" And rDate.Offset(0, -1) = "") Or (rDate = "#" And rDate.Offset(0, -1) = "#") Then
                        rDate.Offset(0, 1) = "Usable (>12)"
                    ElseIf rDate.Offset(0, -1).Value >= DateSerial(Year(Date) - 1, Month(Date) + 1, 1) Then
                        rDate.Offset(0, 1) = "Usable (>12)"
                    ElseIf rDate.Offset(0, -1).Value >= DateSerial(Year(Date) - 2, Month(Date) + 7, 1) And rDate.Value < DateSerial(Year(Date) - 2, Month(Date) + 13, 1) Then
                        rDate.Offset(0, 1) = "Usable (7-12)"
                    ElseIf rDate.Offset(0, -1).Value >= DateSerial(Year(Date) - 2, Month(Date), 1) And rDate.Value < DateSerial(Year(Date) - 2, Month(Date) + 7, 1) Then
                        rDate.Offset(0, 1) = "Near expiry"
                    ElseIf rDate.Offset(0, -1).Value < DateSerial(Year(Date) - 2, Month(Date), 1) Then
                        rDate.Offset(0, 1) = "Expired"
                    End If
                Next rDate
                
                
                srcWB.Close True
            End With
        Else
            Set srcWS = Sheets("base")
            LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            With desWS.Range("C:C,E:E,F:F,J:J,K:K")
                For i = 1 To .Areas.Count
                    LastRow2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                    x = .Areas(i).Column
                    Set Header = srcWS.Rows(1).Find(.Areas(i).Cells(1), LookIn:=xlValues, lookat:=xlWhole)
                    If Not Header Is Nothing Then
                        srcWS.Range(srcWS.Cells(2, Header.Column), srcWS.Cells(LastRow, Header.Column)).Copy desWS.Cells(LastRow2, x)
                    End If
                Next i
            End With
            srcWB.Close False
        End If
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

I'll have a look at the West file and get back to you.

 

[mumps]

Do you want columns from the "West" file to be imported into the "Master" file or "MW Stock" file" or not import any columns at all and just populate columns W and X? If you want to import the columns, again the headers don't match. Please clarify.

 

[mogss_04]

Hi Mumps,

For the new criteria on dates , i tested and found the below mismatches.

1. 12.12.2017, 01.01.2018, 28.02.2018, 30.05.2018 are populated as "Usable (>12)" instead of "Usable (7-12)".
2. 05.05.2017, 04.07.2017, 30.06.2017 are also populated as "Usable (>12)" instead of "Near Expiry".

Also after the values are populated in column N in Belarus 3 file, the columns are not getting copy pasted in master file. "("C:C,E:E,F:F,J:J,K:K,N:N")" range of columns in master file are not getting populated.They are blank. Can you please look into it.

 

[mogss_04]

I want both West and MW stock file to be finally pasted in Master file.Yes the column names do not match. I will change it as per Master file.

Under MW stock file, I changed the headers Item Code - A, Product Name-B and Total Stock-G to Material Code - C, Material- E and Quantity - L columns as per master file.

Under MW stock file the "Salable Stock" - D and "Salable Stock with Distributor Qty" addition should be pasted in Quantity column G and then column A-Material Code, B-Material, C-Country and G-Quantity should be pasted in Master file under similar headers.

For West file , I changed the headers Material-C, Material Name-D, Manufacturing Date-F to Material Code - C, Material- E, Batch Creation Date-J as per Master file. And then i want Columns Material-C, Material Name-D, Manufacturing Date-F, Batch Expiry Date- G, Inventory Flag-W and Inv Flag-X from West file to be pasted in Master file under similar headers.

 

[mumps]

Replace this line of code:

With desWS.Range("C:C,E:E,F:F,J:J,K:K")

with this one:

With desWS.Range("C:C,E:E,F:F,J:J,K:K,O:O")

In order to test the dates issue, I would need you to upload a Belarus 3 file where the expiry date is blank or equals "#" and the manufacturing date is populated. We will look at the other issue with West and MW Stock after we have solved the date problem.

 

[mogss_04]

Hi Mumps,

I have uploaded a copy of Belarus 3 file. Below is the link.

https://www.dropbox.com/s/5wzide7bsoi4sxr/Belarus 3.xlsx?dl=0

And I replaced the code.All columns except the "O" - Inventory Flag column is not being pasted into the master file from Belarus 3 file after it gets populated with values.

 

[mumps]

I'm sorry but after trying for several hours to fix the date problem, I can't seem to find a solution. I always find that working with dates is tricky. Perhaps you can start a new thread explaining the problem and another member with more experience may be able to help.

 

[mogss_04]

No issues Mumps.Thanks for putting in efforts.For now as long as certain values are getting populated, I am okay.I will start a new thread and figure out the date part.Can you please help with the other parts of the project.The MW stock and West file.

 

[mogss_04]

Hi Mumps,

I just figured that "Usable >12 " would do fine since as long as they are not expired quantity they would be eventually classified as Usable. So we can go ahead with the dates as it is working now. Can we now go ahead with the other parts of project.

For the dates part i would still open a thread for my learning and see if we can get a solution to it

 

[mumps]

I am now very confused with all the different workbooks and macros we've been dealing with. Can you please upload updated versions of all the files I will need to sort out the MW stock and West files, including the Master if needed. Make sure all the column headers match. Explain again which are the source files and which is the destination file.