For Each ws in Workbook.Worksheets

[SBF12345]

I am trying to run a For Each loop using the following chunk:

Set COM = Workbooks("COMM_COMBINED.xls")

For Each ws In COM.Worksheets    
    AC = ws.Name
    
    Do While COM.Worksheets(AC).Cells(j, 1) <> ""
    
        Set COMVAR = COM.Worksheets(AC).Cells(j, 1)
        
        z = 5
        
        Do While MAIN.Worksheets("M_" & AC).Cells(z, 1) <> ""
        
            Set MAINVAR = MAIN.Worksheets("M_" & AC).Cells(z, 1)
            
            If MAINVAR = COMVAR Then
                
                MAIN.Worksheets("M_" & AC).Range(MAINVAR, MAINVAR.End(xlToRight)).Copy
                COM.Worksheets(AC).Activate
                Cells(j, 1).Select
                Selection.End(xlToRight).Offset(0, 1).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                
                End If
                
                z = z + 1
                
                Loop
        
                j = j + 1
        
    Loop
    
    Next ws

It seems to be executing nicely, but at the Next ws line the code doesn't jump to the next worksheet in the workbook COM. How is it not catching this line and jumping to the next worksheet in the workbook?

 

[JLGWhiz]

I don't know what you mean by "jump to the next worksheet". The ws variable should change values for the worksheets in the order that they appear on your monitor screen. Have you tried stepping through the code using the F8 function key to see if the value changes for the ws variable? Or another way to do it is to insert a message box immediately after the 'For Each ws' statement.

MsgBox ws.Name

You can then see the name value of the ws variable on each iteration of the loop.

 

[joseulloa22]

Hello,

I am trying to get a formula that searches within a sales order (column A) and tells me if that sales order contains SECT or not (column C).


[TABLE="width: 339"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]SALES ORDER[/TD]
[TD]CUSTOMER[/TD]
[TD]PLANNER[/TD]
[/TR]
[TR]
[TD="align: right"]332456 [/TD]
[TD]HOME DEPOT[/TD]
[TD]SECT[/TD]
[/TR]
[TR]
[TD="align: right"]332456 [/TD]
[TD]HOME DEPOT[/TD]
[TD]GATE[/TD]
[/TR]
[TR]
[TD="align: right"]332550 [/TD]
[TD]THE FENCE MAKER[/TD]
[TD]POST[/TD]
[/TR]
[TR]
[TD="align: right"]332550 [/TD]
[TD]THE FENCE MAKER[/TD]
[TD]GATE[/TD]
[/TR]
[TR]
[TD="align: right"]332550 [/TD]
[TD]THE FENCE MAKER[/TD]
[TD]HARDWARE[/TD]
[/TR]
[TR]
[TD="align: right"]333980 [/TD]
[TD]GIMMICK[/TD]
[TD]HARDWARE[/TD]
[/TR]
[TR]
[TD="align: right"]335790 [/TD]
[TD]PARK FENCE[/TD]
[TD]SECT[/TD]
[/TR]
[TR]
[TD="align: right"]335790 [/TD]
[TD]PARK FENCE[/TD]
[TD]POST[/TD]
[/TR]
[TR]
[TD="align: right"]336000 [/TD]
[TD]HOME DEPOT[/TD]
[TD]HARDWARE
[/TD]
[/TR]
</tbody>[/TABLE]


Any help is greatly appreciated

Thanks
Jose Ulloa

 

[SBF12345]

Thanks for the response,

In response to your first question I intended for the ws variable to change values from on the screen like you mentioned. It doesn't appear to be doing this. I ran it with F8, though I'll have to do it again with an Add Watch for the ws variable. I'll try the msgbox method too. Thanks

As for the post below, you may want to start a new thread...and maybe reread the forum rules.

 

[JLGWhiz]

@joseulloa22, Jose, I am not a forum monitor, but I believe the posting guidelines indicate that you should start your own thread when the objective is unrelated to topic of the current thread.

 

[JLGWhiz]

Thanks for the response,

In response to your first question I intended for the ws variable to change values from on the screen like you mentioned. It doesn't appear to be doing this. I ran it with F8, though I'll have to do it again with an Add Watch for the ws variable. I'll try the msgbox method too. Thanks

As for the post below, you may want to start a new thread...and maybe reread the forum rules.

Yes, I only took a quick look at it, but I did not see anything that would make it exit the For Each loop prematurely. So, it should be changing the variable values. It will not change the screen display, if that is what you were expecting. So, your problem could be in your Do While loop or your IF statements causing it to bypass any execution.

 

[StephenCrump]

It seems to be executing nicely, but at the Next ws line the code doesn't jump to the next worksheet in the workbook COM. How is it not catching this line and jumping to the next worksheet in the workbook?

I agree with JLGWhiz's comments. I suspect your code isn't producing the results you expect, and you are jumping to conclusions about what might be happening.

Post #11 here (where you provided more code) may identify part of the problem: https://www.mrexcel.com/forum/excel-questions/1029190-error-2.html

I am guessing that you want to start at row 3 in each worksheet? If so, you'll need j=3 inside the For each ws ... loop