VBA on For Each over multiple Worksheets

[ShaanB]

Sorry if this is an obvious question, I'm new to VBA but I learn by doing and I'm trying to use this code and it's not working for me...

Basically I have a workbook that has 175 sheets on it. Each sheet has data in column A (UPC codes) and I want to put the number 1 in column B next to every UPC.

I can get the macro to work on an individual worksheet, but I want it to cycle through all the sheets. This is the code I have:

Sub ReplaceOne()
Dim wsheet As Worksheet
Dim Cell As Range
For Each wsheet In ThisWorkbook.Worksheets
For Each Cell In Range("A:A")
If Cell.Value <> "" Then Cell.Offset(0, 1).Value = 1
Next
Next wsheet
End Sub

As I said, the actual action works fine on the active sheet, but it doesn't move on to the next sheet. I've tried it on a smaller data set and no issues.

I've already had to do this once for a slightly smaller data set and I just wrote into the code
ActiveSheet.Next.Select and then used a keyboard shortcut repeatedly, which worked (code below)

Sub ReplaceOne()
Dim wsheet As Worksheet
Dim Cell As Range
For Each Cell In Range("A:A")
If Cell.Value <> "" Then Cell.Offset(0, 1).Value = 1
Next
ActiveSheet.Next.Select
End Sub

What's the issue with the top code? Interestingly, when I added the ActiveSheet.Next.Select into the original code after the first Next statement, it ran through all the sheets. I did however get an error (91, Object variable or With block variable not set) at the end....

Thanks for any help you can give!

 

[ShaanB]

Sorry, I tried it on a smaller data set and SAME issue....

 

[Biz]

Try code below.
Does it help?

Dim aStartTime
Dim ProcName As String
Dim bErrorHandle As Boolean




Sub ReplaceOne()
    Dim wsheet As Worksheet
    Dim Cell As Range
    
    'Start Timer
    aStartTime = Now()
    
    'Speeding Up VBA Code
    Call SpeedOn
    
    On Error GoTo ErrorHandle
    bErrorHandle = False
    
    For Each wsheet In ThisWorkbook.Worksheets
        wsheet.Activate
        For Each Cell In Range("A:A")
            If Cell.Value <> "" Then Cell.Offset(0, 1).Value = 1
        Next Cell
    Next wsheet
    
    If bErrorHandle = False Then
        MsgBox "Time taken: " & Format(Now() - aStartTime, "h:mm:ss") & vbNewLine & " You're good to go!", vbInformation, "Excellent"
    End If
    
BeforeExit:
    Exit Sub
ErrorHandle:
    
    ProcName = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    MsgBox "Procedure: - " & ProcName & vbNewLine & vbNewLine & Err.Description, vbCritical, "Oops I did it again...."
    Resume BeforeExit
    
End Sub


Private Sub SpeedOn()
    'Speeding Up VBA Code
    With Application
        .ScreenUpdating = False 'Prevent screen flickering
        .Calculation = xlCalculationManual 'Preventing calculation
        .DisplayAlerts = False 'Turn OFF alerts
        .EnableEvents = False 'Prevent All Events
    End With
End Sub
Private Sub SpeedOff()
    'Speeding Up VBA Code
    With Application
        .ScreenUpdating = True 'Prevent screen flickering
        .Calculation = xlAutomatic 'Preventing calculation
        .DisplayAlerts = True 'Turn OFF alerts
        .EnableEvents = True 'Prevent All Events
    End With
End Sub