Page 2 of 7 FirstFirst 1234 ... LastLast
Results 11 to 20 of 63

Thread: copy pasting multiple columns from multiple workbooks into one master workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    May 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy pasting multiple columns from multiple workbooks into one master workbook

    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

  2. #12
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,858
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: copy pasting multiple columns from multiple workbooks into one master workbook

    Please ignore.
    Last edited by mumps; May 11th, 2019 at 11:52 AM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #13
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,858
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: copy pasting multiple columns from multiple workbooks into one master workbook

    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.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #14
    Board Regular
    Join Date
    May 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy pasting multiple columns from multiple workbooks into one master workbook

    Sure i would do that and post the link here.

  5. #15
    Board Regular
    Join Date
    May 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy pasting multiple columns from multiple workbooks into one master workbook

    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.

  6. #16
    Board Regular
    Join Date
    May 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy pasting multiple columns from multiple workbooks into one master workbook

    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.

  7. #17
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,858
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: copy pasting multiple columns from multiple workbooks into one master workbook

    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 .
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  8. #18
    Board Regular
    Join Date
    May 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy pasting multiple columns from multiple workbooks into one master workbook


  9. #19
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,858
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: copy pasting multiple columns from multiple workbooks into one master workbook

    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 by mumps; May 17th, 2019 at 09:21 AM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  10. #20
    Board Regular
    Join Date
    May 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: copy pasting multiple columns from multiple workbooks into one master workbook

    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/5wzide7bso...%203.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/trc9tbng86...arus.xlsx?dl=0


    https://www.dropbox.com/s/5wzide7bso...%203.xlsx?dl=0


    Let me know if you need anything else.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •