Can't loop through worksheets

[VBAWannabee2]

Hi All!

I have this code but I can't work it out to loop thru worksheets.
What it does is per sheet in my workbook, it copies the values on Column B to Column C (Only the visible cells because the data on all sheets was subtotaled)
Hope my explanation is clear enough. Please see screenshot also. Thanks for any assistance!

Dim ws As Worksheet
Dim rng As Range

For Each ws In ActiveWorkbook.Worksheets

If ws.Visible = True Then

    For Each rng In Range(Cells.Find(What:="Total"), Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas
      rng.Offset(, 1).Value = rng.Value
    Next rng
    
End If
    
Next ws

 

[johnnyL]

Perhaps change:

    For Each rng In Range(Cells.Find(What:="Total"), Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas

to:

    For Each rng In ws.Range(Cells.Find(What:="Total"), ws.Range("B" & ws.Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas

 

[VBAWannabee2]

Perhaps change:

    For Each rng In Range(Cells.Find(What:="Total"), Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas

to:

    For Each rng In ws.Range(Cells.Find(What:="Total"), ws.Range("B" & ws.Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas

Hi John! Thanks for the reply but I got an error though. 1004.

 

[Michael M]

UNTESTED

Sub MM1()
Dim ws As Worksheet, rng As Range
For Each ws In Worksheets
If ws.Visible = True Then
With ws
    For Each rng In .Range("B" & Rows.Count).End(xlUp).SpecialCells(xlVisible)
      If InStr(rng.Value, "Total") Then rng.Offset(, 1).Value = rng.Value
    Next rng
 End With
End If
Next ws
End Sub

 

[VBAWannabee2]

UNTESTED

Sub MM1()
Dim ws As Worksheet, rng As Range
For Each ws In Worksheets
If ws.Visible = True Then
With ws
    For Each rng In .Range("B" & Rows.Count).End(xlUp).SpecialCells(xlVisible)
      If InStr(rng.Value, "Total") Then rng.Offset(, 1).Value = rng.Value
    Next rng
 End With
End If
Next ws
End Sub

Hi Sir Michael!

I'll get back to you. I've been running it multiple times but it freezes my excel.
So I didn't know if it works or not. Thanks though!

 

[VBAWannabee2]

Hi Sir Michael!

I'll get back to you. I've been running it multiple times but it freezes my excel.
So I didn't know if it works or not. Thanks though!

Hi again Sir Michael,

I don't think it works that well?? Though it showed in the 1st sheet that it copied the values of Column B to Column C but I don't know the rest of the sheets since it freezes the whole workbook.

This code works really perfectly fine, it's just I need it to work on loop thru worksheets.

For Each rng In Range(Cells.Find(What:="Total"), Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas
      rng.Offset(, 1).Value = rng.Value
    Next rng

 

[Fluff]

How about

Dim ws As Worksheet
Dim rng As Range

For Each ws In ActiveWorkbook.Worksheets

If ws.Visible = True Then

    For Each rng In ws.Range("B2", ws.Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas
      rng.Offset(, 1).Value = rng.Value
    Next rng
    
End If
    
Next ws

 

[VBAWannabee2]

How about

Dim ws As Worksheet
Dim rng As Range

For Each ws In ActiveWorkbook.Worksheets

If ws.Visible = True Then

    For Each rng In ws.Range("B2", ws.Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas
      rng.Offset(, 1).Value = rng.Value
    Next rng
   
End If
   
Next ws

Hi Fluff!!

Works gorgeously!! Big thanks!!

I'm still analyzing the code but I think I've got it.

Thank you very much!!!

 

[VBAWannabee2]

Hi Fluff!!

Works gorgeously!! Big thanks!!

I'm still analyzing the code but I think I've got it.

Thank you very much!!!

Hi Fluff!

Apologies for another reply, but where can I put this line of mine?
After copying the values from Col B to Col C, I need to replace the word "Total" & "Grand" to "" (blank). Also, is my code correct? I keep getting range class failed.
Thanks in advance!!

ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp)).Select

    With Selection
            .Cells.Replace What:="total", Replacement:=""
            .Cells.Replace What:="grand", Replacement:=""
            .Columns.AutoFit
    End With

 

[VBAWannabee2]

Hi Fluff!

Apologies for another reply, but where can I put this line of mine?
After copying the values from Col B to Col C, I need to replace the word "Total" & "Grand" to "" (blank). Also, is my code correct? I keep getting range class failed.
Thanks in advance!!

ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp)).Select

    With Selection
            .Cells.Replace What:="total", Replacement:=""
            .Cells.Replace What:="grand", Replacement:=""
            .Columns.AutoFit
    End With

Kindly disregard this one. I've got it! Case solved! Thanks!!!