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]

Master is the one with the code, it doesn't matter what the name of it is because when you set wkbDest in the code, you are setting it as 'thisworkbook' because that is the workbook with the code.
I think it's not the name of the workbook that is an issue, it's the name of the worksheet.
Refer to post #6

 

[MadhukarG]

Master is the one with the code, it doesn't matter what the name of it is because when you set wkbDest in the code, you are setting it as 'thisworkbook' because that is the workbook with the code.
I think it's not the name of the workbook that is an issue, it's the name of the worksheet.
Refer to post #6

I have changed the Destination file from "Master" to Group Inventory Analysis July 2022" and also changed the word from "Master" to yellow highlighted in code line below, but still getting same error message Runtime error #9 and when debugging the below line is highlighted in yellow.

.Sheets("Group Inventory Input").Range("C8:E" & LastRow).Copy wkbDest.Sheets("Group Inventory Analysis July 2022").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

 

[davesexcel]

Is there a sheet named "Group Inventory Analysis July 2022" in the destination workbook(wkbDest)?

I can't see what the problem is, just guessing from what you are saying.

 

[MadhukarG]

Is there a sheet named "Group Inventory Analysis July 2022" in the destination workbook(wkbDest)?

I can't see what the problem is, just guessing from what you are saying.

Hi Dave - I have corrected the above by changing the name of sheet in code for destination file, however the macro is working fine but with few errors so,, want your assistance on below.

1. The destination file has data from prior period every month as I roll forward prior month file to subsequent month, if I want to overwrite the data with current month source files data then, what code should be used to overwrite? and in destination file I want to start copying data from cell C15 to E.
2. Once the data is pasted from source files, I want the data should be pasted as paste special values.
3. Also while running the macro I am getting one message as the destination file contains external links and do you want to update it., I don't want to update those external links, so can we put any code to ignore such message?
4. Though I am able to run the macro and data is coming in destination file from source files but, still getting Runtime error #9 and below code is highlighted.

 

[davesexcel]

You could be opening a workbook that does not have that sheet.

Paste special is different. You require two lines for copy and pastespecial
ie

Range.copy
range.pastespecial xlpastevalues

 

[MadhukarG]

You could be opening a workbook that does not have that sheet.

Paste special is different. You require two lines for copy and pastespecial
ie

Range.copy
range.pastespecial xlpastevalues

Dave - I have checked all 30 source files and see all the workbook has sheet "Group Inventory Input"
and also in which line I need to paste the code of paste special?

Thank you
Madhukar

 

[davesexcel]

Hit the search button and look up "pastespecial"
You need to practice and experiment.

 

[MadhukarG]

Hit the search button and look up "pastespecial"
You need to practice and experiment.

sure Dave, thank you very much for guiding me through.