Results 1 to 2 of 2

Thread: Excel VBA (Copy data from multiple workbooks into a Master file)

  1. #1
    Seeker saadt's Avatar
    Join Date
    Sep 2012
    Posts
    14
    Articles
    0
    Excel Version
    2010

    Excel VBA (Copy data from multiple workbooks into a Master file)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone,



    I have a Master that looks like this in one folder. Lets say the file path for Master sheet is C:\Users\John\OneDrive\Desktop\Test



    And then every month I will get a new monthly file in a different folder with a file path of C:\Users\John\OneDrive\Desktop\Test\American Express and the file name will be the date (matches with the column name of Master file). The monthly file will look like this:



    Now I'm trying to grab the total of the value highlighted in yellow in the monthly file and automatically paste it into the right column of Master file, every time a new monthly is saved in that folder.

    Would appreciate your help
    Thanks!



    Here is my code but I'm getting an error "subscript out of range". Also, I do not know how to tell VBA to automatically grab data when the new monthly file is saved in the folder and paste its data into the Master file under the right column


    Sub
    AmericanEx()

    Dim x As Workbook
    Dim y As Workbook

    '## Open both workbooks first:
    Set x = Workbooks.Open("C:\Users\John\OneDrive\Desktop\Test\American
    Express\2018-02-28"
    )
    Set y = Workbooks.Open("C:\Users\John\OneDrive\Desktop\Test\Master file")

    'Now, copy what you want from x:
    x
    .Sheets("Monthly Sheet").Range("K26").Copy

    'Now, paste to y worksheet:

    y
    .Sheets("Master Sheet").Range("B6").PasteSpecial

    'Close x:
    x
    .CloseEnd S


    Last edited by saadt; 2018-05-22 at 03:54 AM.

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,709
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please post your workbook with the macro. Pictures are impossible to work with. Thank you
    Thank you Ken for this secure forum.

Tags for this Thread

Posting Permissions

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