copy pasting multiple columns from multiple workbooks into one master workbook

mogss_04

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

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

mumps

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

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

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

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

mumps

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

mogss_04

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

Watch MrExcel Video

Forum statistics

Threads
1,096,307
Messages
5,449,579
Members
405,573
Latest member
Diogo Martins

This Week's Hot Topics

Top