Copy Data from Multiple workbook into one workbook

[MadhukarG]

Hi All,

I have one recurring task to copy the data from multiple excel files data received in fixed format from column C to E and from row 8 and paste the data in one master sheet one after one (data in rows can change based on business).

The multiple workbooks are saved on one path.

I want your help to draft one macro to copy the data from multiple workbooks into one workbook. (if possible Pls write text to explain as what each code performs).

Below is the data received monthly format, I need Cons Unit, Partner unit and Net book value (compile data from row 8).

Thank you
Madhukar

 

[davesexcel]

Hit the search button the the top of this page and search for "loop through folder"

 

[MadhukarG]

Thank you for guiding Dave - I have got below code from one thread and used it by changing the folder path and sheet name but, getting Runtime error #9 message and below code line is highligted the text in red. Dont know where I am going wrong or anything needs to be updated. I am macro learner so, cant understand much on error message.

Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Const strPath As String = "C:\Users\Madhukar.Galpalli\Downloads\PII Inventory\"
ChDir strPath
strExtension = Dir("*.xls*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
LastRow = .Sheets("Group Inventory Input").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Sheets("Group Inventory Input").Range("C8:E" & LastRow).Copy wkbDest.Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Close savechanges:=False
End With
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[davesexcel]

#9 could mean subscript out of range.
Do the sheet names exist or are the spelled correctly?

 

[MadhukarG]

#9 could mean subscript out of range.
Do the sheet names exist or are the spelled correctly?

yes its exist, in all source files but, in master file it doesn't as we need to paste the data in first sheet.

 

[davesexcel]

Would it work if you change the sheet name from "Master" to the correct name?

 

[MadhukarG]

Would it work if you change the sheet name from "Master" to the correct name?

The correct file name in actual scenario I will use is "Group Inventory Analysis July 2022" but, for testing the macro I was using destination file name "Master" and the destination file name suffix would change periodically with replacing Month and year at once.

The Source file names would be different based on company name etc. and within these files the data is present in sheet "Group Inventory input", I need to copy the data from all these files into one file i.e. Master file (I want to change the Master file name to "Group Inventory Analysis July 2022" while using in live environment. Only for test purpose I have changed the destination file name to "Master".

Happy to chat for quick resolution from you.

 

[MadhukarG]

The correct file name, I will use is "Group Inventory Analysis July 2022" but, this destination file (i.e. Masterfile) name suffix would change periodically with replacing Month and year at once.

And Master is not sheet name in source excel files, the sheet name is "Group Inventory Input", the master is destination file name in which I need all data from multiple files.

 

[davesexcel]

isn't wkbDest the destination workbook?

 

[MadhukarG]

isn't wkbDest the destination workbook?

Sorry, don't know what that mean to me, as I am just beginner to learn the Macro from you all experts.
For example, below is the screenshot where first four files are source files and last file "Master" is destination file but, Destination file name would be changed when macro runs successfuly.

I can change the destination file name to "Group Inventory Analysis July 2022" if that makes easy for us, but the suffix of this destination file name would change monthly like, July 2022, Aug 2022, Sep 2022 so on....