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

[saadt]

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

 

[Pecoflyer]

Please post your workbook with the macro. Pictures are impossible to work with. Thank you