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.

 

[mogss_04]

Hi Mumps,

After i pasted the code in module it worked. Also the file names were "Belarus 2" and not "Belarus2". I rectified that. However in column L of the master file the quantity in numbers is not getting pasted. I applied the paste special function ".Range("P2:P" & LastRow2).Copy desWS.Cells(LastRow1, "L").PasteSpecial xlPasteValues" this way . I am getting compilation error at the end of statement.

Thank you

 

[mumps]

Please ignore.

 

[mumps]

I don't know why you would be getting that error. Perhaps you could upload a copy of your destination workbook and the "Belarus" workbook to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. If the workbooks contain confidential information, you could replace it with generic data.

 

[mogss_04]

Sure i would do that and post the link here.

 

[mogss_04]

Hi Mumps,


1. I figured out the paste special function. Now it works fine. I wanted to know whether if the number of workbooks are not fixed and keep on changing.For example in the above scenario we had Belarus, Belarus 2 and Belarus 3 files in the folder. If i have multiple Belarus files and the number of workbooks is not fixed every month, how would i go about the coding in this scenario of copy pasting the data from these multiple files into the master file.


2. Also under Belarus 3 file- C:\Users\Priyanka Singh\Desktop\VBA code1
there is a coulmn name "Usage - I" which has the below 5 categories data . So 1st case we need to filter first by "Unrestriced use" and "Unrestricted-Use Mat" and then need to filter column "M - Batch Expiry Date". So if the expiry date falls 12 months after the the current month i.e 2020 June onwards then column N should be polpulated as"Usable (>12)", if expiry date falls between 7 - 12 months after expiry date
which is from December 2019 - May 2020 then column N should be polpulated as"Usable (7-12)" . Under "Unrestriced use" and "Unrestricted-Use Mat" anything before May 2019 that is current month should be populated as
"Expired" and if expiry date is from " May 2019 - November 2019" then column N should be poulated as "Near expiry".
Next we need to filter by "Blocked Stock" under column I and if it is Blocked stock then column N should be populated as Blocked irrespective of the expiry date column - M . Similary if column I is "Transit and Intransit" then column N should be populated as "Transit".


1 Unrestricted Use
2. Transit
3.Blocked Stock
4.Unrestricted-Use Mat
5. Intransit


We need to apply double condition of Expiry date - M only under "Unrestriced use" and "Unrestricted-Use Mat". Can you let me know how to go about the code the above.

 

[mogss_04]

Under scenario 1, I have one master file and I need to paste the data from workbooks under path C:\Users\Priyanka Singh\Desktop\VBA code . The name of all Workbooks is 1 Export warehouse & BSR inventory April_19_1, 1 Export warehouse & BSR inventory April_19_2, 1 Export warehouse & BSR inventory April_19_3, 1 Export warehouse & BSR inventory April_19_4 and this way i can have mutiple workbooks every month. I need to combine the data from all this workbooks into one master file.

I need the entire data from Columns E8 to AR of every worbook into master file under columns A4:AN . Can i have a macro which can run even if there are more than 4 workbooks and create one master file.

 

[mumps]

Please upload a copy of the master file, a copy of the "Belarus 3" file and a copy of one of the other source files as described in Post #13 .

 

[mogss_04]

https://www.dropbox.com/s/480xgkmjey4fiuu/Belarus 3.xlsx?dl=0

https://www.dropbox.com/s/458qvmdnys7mtjf/Belarus.xlsx?dl=0

https://www.dropbox.com/s/5jdy0pihmh4vlmx/master file.xlsm?dl=0

I have posted Belarus, Belarus 3 and master file. Please look into it

 

[mumps]

It doesn's matter how many Belarus files you have.

There are several issues that need to be cleared up.
I don't see "Unrestricted-Use Mat" or "Transit and Intransit" as an option in the drop down in column I - "Usage" in the Belarus 3 file. The drop down also has an option "Quality Inspection" which you didn't list in Post #15 .

You have referred to 2 different folder paths: "C:\Users\Priyanka Singh\Desktop\VBA code" and "C:\Users\Priyanka Singh\Desktop\VBA code1"
You have also referred to 2 different types of files: "Belarus" and "Export warehouse & BSR inventory"
Please clarify which files are in which folder (VBA code and VBA code1).

Belarus and Belarus3 have different structures, different number of columns and different headers. What structure do the other Belarus files have?

I'll have some more questions depending on the answers to the above questions.

 

[mogss_04]

Hi Mumps,


Please refer to the below file link for Belarus 3 and Belarus 2 files. Sorry i forgot to mention the other 2 categories as below.


Quality inspection - to be populated as "Quality inspection" under column N irrespective of expiry date
Valuated Goods Receipt Blocked Stock- to be populated as "Blocked" under column N irrespective of expiry date


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


Please refer to the below link "C:\Users\Priyanka Singh\Desktop\VBA code1"


Please ignore "Export warehouse & BSR inventory" file.


Also Belarus and Belarus 3 files are in different formats. I want the column names "Material Code", "Material" , "country", "Batch Creation Date" and
"Batch Expiry Date" which are common names in both files. These columns can be at different places in multiple files and i want these columns to be combined and form a master file.


https://www.dropbox.com/s/trc9tbng862pk9b/Belarus.xlsx?dl=0


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


Let me know if you need anything else.