Copy sheets with the same name from multiple workbooks

Hi guys!

I found a really useful forum post here, but I don’t really know what I need to amend to get the VBA code to perform in the way that I would like it to.

I have a significant number of workbooks which I would like to collect data from. Each workbook is structured in the same way with the sheets named ‘Key Stage 1’, ‘Key Stage 2’, ‘Key Stage 3’ and ‘Key Stage 4’.

The workbooks are closed but saved in one folder. I need all the data from the sheet ‘Key Stage 1’ from all the workbooks consolidated into a new sheet in a new workbook in the same folder.

I need to repeat this process for Key Stage 2, 3 and 4, so that having run the code/macro I have a workbook in the same folder with 4 sheets consolidating all the data (Key Stage 1, 2, 3 and 4) from all the workbooks.

How do I do this?

I look forward to hearing from you.

Christian :)

Thank you!!!

I will try this and let you know how I fare

Chris

Hi Arlette

I'm getting the following run-time error:

Attachment 194201

What do I need to do?

Thank you!

Chris

Hi Arlette

The macro works, but I wondered if you could help me tweak it so that it will perform the way I would like it to.
I would like the macro:

- to also retrieve data from a sheet titled 'EYFS'
- to copy data from the second row onwards, as the spreadsheets have row headers
- to ignore a worksheet if it does not contain any data in the 2nd row onwards

Attached is the workbook produced as a result of running the macro.

Key Stage 4 - should have three rows of data
Key Stage 3 - should have two rows of data
Key Stage 2 - should have no data (because the target source does not have data)
Key Stage 1 - should have no data (same as above)

Thank you for your help in developing this macro so far - I really appreciate it :)

Christian

Each of the target workbooks, contain the following sheets: EYFS, Key Stage 1, 2, 3 & 4.

Different users will only access one or two of the sheets which are relevant to them, so if I received three workbooks they may contain data as follows:

Workbook 1 - Key Stage 1
Workbook 2 - Key Stage 1 & 2
Workbook 3 - Key Stage 3
Workbook 4 - Key Stage 3 & 4
Workbook 5 - EYFS & Key Stage 1

All of the data should go into the relevant sheet in the master workbook.

Does that make sense?

Sorry - my bad. I figured I would be able to tweak the code, but on seeing it I was baffled.

Each workbook will have some data recorded - might be lots or very little - but all the workbooks will have some data. If a workbook has ten data entries on the KS2 sheet, the code will need to search all the sheets, realise that they are blank and ignore them - only transferring the data on the KS2 sheet.

Thank you for all your help so far - I appreciate your time, effort and expertise

Chris

Hi Arlette

I will try this and let you know how I go.

Thank you for all your help.

Christian

Hi Arlette

I got the following compile error when I ran the macro:

'Variable not defined'

It identified

Please Login or Register  to view this content.

Does it matter my spreadsheets use iRows?

Christian

Hi Arlette

I declared the iRow, but then got the same error as seen in Post #4.

I think you said it was a typo - what do I need to adjust?

(I feel like Thomas Edison on the cusp of a breakthrough!)

I've done that - the runtime error is 1004:

The method 'Open' of object 'Workbooks' failed.

Hi Arlette

I am using the same code as post #11. I have declared the lRow as asked. When I run the macro, a runtime error '1004' saying there is an issue in the method 'Open' of object 'Workbooks'.

Is it possible that Excel 2003 does not accept that method?

Whereas before the macro, would return data, currently all that is happening is that the workbook is being populated with blank sheets called 'Key Stage 1, 2, 3 and 4'. There is no EYFS sheet being populated.

I will upload the documents shortly.

Hi Arlette

This is the code I am using:

Please Login or Register  to view this content.

If the following three workbooks (Test 1, Test 2, Test 3) were the target sources, MasterBook demonstrates how I would like the macro to retrieve the data from each. At present, it's not doing that.

Where am I going wrong?

Chris

Hi Arlette

I'm still having difficulties - nothing is happening now.

I have a workbook called 'Summary.xls' in the same folder as the other workbooks. I have created the worksheets for the data to be stored in. I have placed the code in 'Summary' and when I run the macro, nothing happens.

The workbooks are password protected and when I would run the initial macro from post #2, I would be asked to type in the password for each workbook - so it was clear the workbooks were been accessed. At present, it doesn't seem as though they are.

Is there something I am missing?

Here is the code I am using:

Please Login or Register  to view this content.

Hi Arlette

Still no joy.

I have made sure the path is correct. The macro is not prompting me to enter the password; before it was.

I'm totally baffled.

Hi Arlette

Yes - the macro resides in the master workbook ('Summary') and that is where the macro is being run from. The master is the only workbook open at teh time of running. Previously, the macro opened the closed workbooks - now it doesn't.

Hi Arlette

Please see the attached files.

Thank you

Chris :)

Arlette,

I think you may have cracked it!!!!!! Will test on my live document when I get into work.

Thank you for all your help so far!

Chris

Hi Arlette

The macro is working, although when I run it the following dialogue box appears:

Attachment 195516

Having clicked no, the following dialogue box appears:

Attachment 195517

When I click 'End', the macro completes the transfer.

Is there something I need to adjust to make sure that I don't get the second dialogue box?

Thank you for all your help.

Chris :)

Hi Arlette

The first dialogue box says:

'Summary.xls is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen Summary.xls?'

The second dialogue box says:

'Run-time error '1004':
Method 'Open' of object 'Workbooks' failed

Yes - the macro is saved in Summary.xls.