Looping Worksheet Macro - Skip a Sheet

Hi,
I have this macro to cycle through all worksheets, retrieve data and paste into the "Test Lookup" sheet, but I want to skip one worksheet (called "Specs").

Any thoughts on how? Thanks.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Try this...

Please Login or Register  to view this content.

I just realised it's still going back to the "Spec" sheet at the end and copying and trying to paste data (but erroring as there's no data in those cells).

Any ideas? Thanks.

Hi tudley,

Here's how you could do it with your existing code:

Please Login or Register  to view this content.

HTH

Robert

Originally Posted by tudley

I want to skip one worksheet (called "Specs")

Please Login or Register  to view this content.

Originally Posted by tudley

I just realised it's still going back to the "Spec" sheet at the end and copying

I given code based on your above statements.

If your sheet name is spec not specS then correct it in the provided code.

Originally Posted by Trebor76

Hi tudley,

Here's how you could do it with your existing code:

Please Login or Register  to view this content.

HTH

Robert

Thanks, I still get a runtime error as it's copying all the blank data in the "Specs" worksheet.

Originally Posted by :) Sixthsense :)

I given code based on your above statements.

If your sheet name is spec not specS then correct it in the provided code.

Sorry, my typo, the worksheet name is "Specs".

Also realised it was looking up data in blank sheets at the end. Deleting them and all good, except it runtime errors at "ws.select" when it gets to the end...

Originally Posted by tudley

except it runtime errors at "ws.select" when it gets to the end...

I believe there is some availability of hidden worksheet(s)

Try this revised code

Please Login or Register  to view this content.

Thanks, I still get a runtime error as it's copying all the blank data in the "Specs" worksheet.

I don't know what you mean as it should do nothing with that tab - it didn't for me when I created a workbook and called one of the tabs "Specs"

Are you sure there's no trailing spaces in the tab name?

@ Trebor76 - I thought that was strange too - just renamed the sheet again and it now skips it - working as well, so I have two options, thanks!

There must have a space (or spaces) either at the start and/or end of the tab name.

If you could mark the thread as solved it would be appreciated.

Regards,

Robert