Looping through worksheets

[terkel]

Hi there
This looping is driving me loopy. I hope some one can tell me what I’m doing wrong. I’ve searched the topic but can’t find anything that helps me sufficiently.
I have a workbook (“Per01.xls”) with 21 sheets in it, each one with a name tab with a different date plus one called ‘Menu’. These sheets are locked and password protected and I don’t know the password. I’m copying parts of each sheet into a new workbook(“Per01Combined.xls”). This workbook has 4 sheets in it called Wk 1, Wk2 etc. I copy the data I need from the first 5 sheets from “Per01.xls” into sheet Wk1 in “Per01Combined.xls”, then the next 5 sheets from “Per01.xls” into sheet Wk2 in “Per01Combined.xls etc. I have set up and modified a macro to do this and it works perfectly but need to run it 20 times with a lot of clicking backwards and forwards between workbooks so I want to be able to loop through the worksheets to do it. With the code I have it copies the page active sheet in “Per01.xls” 20 times into the active sheet in “Per01.xks combined”i nstead of copying each of the 20 pages just once.

Can anyone tell me the code I need to use to
a) have it copy each sheet just once; and
b) have it copy the first 5 sheets into Wk1, the next 5 into Wk2 etc.

Below is the code I am using:

Please Login or Register  to view this content.

As always, thanks for the great assistance received from this forum.

[ravishankar]

Hi
Open both files and paste these codes into per01.xls and run the macro.

Please Login or Register  to view this content.

If you get an error check for spaces in sheet name like WK 1 instead of WK1
Ravi

[terkel]

Hi Ravi
Thanks for that advice - but before I try it I forgot to mention that I run the macro from the workbook "Per01.combined.xls". Will that make any differnce to what you have said?

[terkel]

Hi Ravi ,again
I just tried to do what you advised but then realised that "Per01.xls" is password protected. Everything has to be run from "Per01Combined.xls".

Thanks
Terk

[ravishankar]

Hi
try this modification from combined file

Please Login or Register  to view this content.

Ravi

[terkel]

Hi Ravi
It's nearly working I think but there's a sheet in "PER01" called 'Menu' that needs to be excluded from the process. This sheet is causing the copy/paste process to paste incorrectly. I've tried to exclude the sheet "Menu" by inserting the code below into what you have provided but when the code just activates "PER01" then stops. If I step through the macro I can see that it skips your code entirely and just goes to the 'End If' statement. Can you tell me how to make it read what you have provided.

Code below.

Many thanks for your assisitance
Terk

Please Login or Register  to view this content.

[ravishankar]

Hi

Please Login or Register  to view this content.

Ravi

[terkel]

Hi again Ravi
Thanks so much for persisting with this.
When I tried your code it still picked up the sheet 'Menu' and did all sorts of strange things with the pasting. So I stuck in the line

Please Login or Register  to view this content.

at the top of your code and it now ignores the sheet 'Menu'. However, instead of pasting the first 5 sheets from "PER01" into the sheet 'Wk1' in "PER01Combined, the next 5 into sheet 'Wk2' etc, the following is happening.

Pastes sheets 1 - 3 into Wk1
Pastes sheets 1 - 9 into Wk2
Pastes sheets 1-3; 10-13 into Wk3
Pastes sheets 1-3; 14-19 into Wk4.

Complete code is below. Hope you are able to help.

Terk

Please Login or Register  to view this content.

[terkel]

Hi there
First of all, I'm a bit disappointed that no-one replied to my last post when I was seeking further help on this problem. But then again, because of that I took the code that Mr Ravishankar had kindly provided me with and worked with that until I finally came up with the solution. The final code that works as I want it to is as follows:

Please Login or Register  to view this content.

I could never in a hundred years have come up with the clever initial code supplied by Mr Ravishankar so thanks for the help provided and I'm sure I'll be back with more problems.

Terk