Code to Loop through Each Worksheet in ThisWorkbook.Worksheets

I'm hoping this is an easy fix. The macro will not select the next sheet "ws" in ThisWorkbook. The message I get on the highlighted line of code is "Method 'Select' of object'_Worksheet' failed" on the attempt at a second run through the code; everything works fine tthr first time through.

Please Login or Register  to view this content.

I think you would not need to use

Please Login or Register  to view this content.

but

Please Login or Register  to view this content.

Since now it is read as

Please Login or Register  to view this content.

Remove the With ws and the End With lines

Originally Posted by ArnolddG

I think you would not need to use

Please Login or Register  to view this content.

but

Please Login or Register  to view this content.

Since now it is read as

Please Login or Register  to view this content.

I'm getting the same error message with this.

Below is the section of code I changed.

Please Login or Register  to view this content.

Originally Posted by stnkynts

Remove the With ws and the End With lines

I'm getting the same error message here as well.

Below are the sections of code I changed.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by AB33

Please Login or Register  to view this content.

I'm now receiving the following message at the highlighted line of code. "Method 'Range' of object'_Worksheet' failed"

Please Login or Register  to view this content.

Please do not reply with quote.

Please Login or Register  to view this content.

A dots are missing. Check my code

Great! Now, I'm debugging at the below edited line of code. I assumed I needed to add the dots in front of "Range" here as well. This works for the first pass but on the second loop it's debugging. It also appears to be creating the Ranges based on the first sheet (rather than the second) in the second loop so I don't think it's actually going to the "Next ws"

Please Login or Register  to view this content.

Please Login or Register  to view this content.

If it works for the first sheet, it is because it found "CHEMICALS". If it does not found, it will error.
Please note this is not a complete code, just merely to give you an idea how to set the find function.

Please Login or Register  to view this content.

Thanks a lot AB33 for your help thus far. I think we're close however if "CHEMICALS" can't be found I have other problems then. The key words are exactly the same for each tab and each tab has all of them in the same columns. The debugging message I'm receiving is: "Run-time error '91': Object variable or With block variable not set" What I've noticed is the .Range lines of code will work on whatever Sheet I have currently selected and NOT loop to the next Sheet unless I manually select it. Therefor it's trying to repeat the same code over the same ranges. If I hover over the highlighted section of code below, VBA shows me the correct range of (in this case) L11:L38.

Please Login or Register  to view this content.

I'm not sure why the code works the first time on a set of ranges and then bugs out on the second loop over the same set of ranges, regardless of the fact that it isn't looping to the "Next ws", but that's question I'm not sure I need answered at this time.

Could you please attach the sample book?

I noticed the code will only run on the first tab but will reference the ranges for whatever Sheet is the active sheet.

Okay!
A couple of points on your code.
It is a good practice to use Option Explicit in your code(At the top). Since mine is default set, I get an error( Not defined variable). You have lots of undefined variables.
The code loops through all sheets, including hidden sheets. You have 3 sheets in the collection, but 2 are shown. Since the code loops through all sheets, it errors on the hidden sheet.

Please Login or Register  to view this content.

ABB33, thank you for the advice. I'm not sure how to define "CHEMICALS" and "COAL" in this very short sample which I need to do if I'm going to use Option Explicit. I also noticed VBA was counting 3 sheets but I don't know where the third sheet is; if you try to unhide it, there's nothing to unhide so I don't know how VBA comes up with 3 sheets. Finally, I still don't know why VBA wont move to the "Next ws". The commands under 'CREATE HEADER are very basic and should be executed on the "Next ws" after the first loop but that isn't happening which leads me to beleive that's were the real breakdown is occuring; VBA isn't actually moving to the next sheet.

Thanks Norie!

This is now my code and it still debugs at the highlighted code on the second pass through. "Object variable or With block variable not set"

Please Login or Register  to view this content.

The code loops through the sheets, except the hidden sheet (Please check my last post). If the loop skips that(Which is hidden) sheet, the code does not error and you should be able to see data in to visible sheets though, I do not know what the code does.
As I said on previous post, the code will error if it could not find the searched item, hence you need to built error trap on the find function.

Okay, so I took the code I had and added it to a new worksheet with just 2 tabs (which is what I thought I had to begin with) and now the code will loop fully twice, which is for the number of sheets I have in this file. It works great but for one exception. My ranges which are defined by the location of "CHEMICALS" and "COAL" are the ranges for whichever Sheet I have as the active sheet when I run the macro; so the range is not corrrect for one of the sheets buts works perfectly for the other. Is there a better alternative for what I'm trying to accomplish than the following?

Please Login or Register  to view this content.

I'm getting an "Application-defined or object-defined error" with the following line of code.

Please Login or Register  to view this content.

AWESOME! Thank you so much Norie and AB33 for all of your help on this one! It's working like a charm.