Copying data from multiple workbooks into master workbook

[vkmummer]

Hello,
I have multiple macro enabled workbooks in the same folder where master file is located.
In all workbook there is a sheet named” PR”. I want to open and copy ranges from B5:D5 up to last row containing values (non-empty cells), then it shall be pasted in master workbook in sheet (“Combined”) from the range B7:D7.
While copying each workbook data, the values shall be pasted in master workbook in subsequent rows.
When the data from one workbook is pasted in master sheet. Last row (B:D) shall be highlighted in yellow.
Finally, serial numbers from 1,2,3 ect shall be provided fin master sheet from A7 onwards upto last row.

I have attached sample files for reference.

[jindon]

Try

Please Login or Register  to view this content.

[vkmummer]

Hi Jindon,

it is not working.

[jindon]

Such comment doesn't help at all.

[vkmummer]

Hello Jindon,

It was not working when i copied into module. but when i downloaded your attachments, it is copying everything perfectly. Thank you for the swift response.

please include below requirement also

- When the data from one workbook is pasted in master sheet. Last row (B:D) shall be highlighted in yellow.
- Finally, serial numbers from 1,2,3 etc shall be provided fin master sheet from A7 onwards upto last row.

[jindon]

Change to

Please Login or Register  to view this content.

[vkmummer]

now the dats is pasting in master sheet from row 2 itself. actually it shall be begin with row7.please check attached sheet with new code.

[jindon]

OMG!
Why are you changing the format???

Upload the workbook with before and after combined sheet.

[vkmummer]

here it is

[jindon]

Which code are you referring to?

It is running as expected if you delete "x"s in row 1007.

If you are using other code that is attached to your workbook, ask to the author.

[vkmummer]

Hello Jindon,

Thanks you very much. it is working as expected. I need one more help, if i have a sheet named "XXXX" instead of "PR" and the sheet is hidden in all workbook. I need to unhide and copy the data from "XXXX" and again hide "XXXX" sheet after pasting the data in master sheet.

Can you please modify the code as per the above requirement.

[jindon]

No need to MODIFY, just change sheet name from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[vkmummer]

Hi,

It is getting run time error since "XXXX" is hidden. it is working when XXXX is not hidden. So code might need to modify in such a way that "XXXX" unhide first then copy then hide it again.

[jindon]

My excel and yours are probably different.

[vkmummer]

Hello Jindon,

now everything working fine. Thanks a lot for you amazing help.