Can't loop through worksheets

VBAWannabee2

New Member
Joined
Feb 23, 2022
Messages
28
Office Version
  1. 2010
Platform
  1. Windows
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!

VBA Code:
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
 

Attachments

  • Capture.JPG
    Capture.JPG
    62.1 KB · Views: 18

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Perhaps change:
VBA Code:
    For Each rng In Range(Cells.Find(What:="Total"), Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas

to:
VBA Code:
    For Each rng In ws.Range(Cells.Find(What:="Total"), ws.Range("B" & ws.Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas
 
Upvote 0
Perhaps change:
VBA Code:
    For Each rng In Range(Cells.Find(What:="Total"), Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas

to:
VBA Code:
    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.
 
Upvote 0
UNTESTED
VBA Code:
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
 
Upvote 0
UNTESTED
VBA Code:
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!
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
How about
VBA Code:
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
 
Upvote 0
Solution
How about
VBA Code:
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!!!
 
Upvote 0
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!!

VBA Code:
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
 
Upvote 0
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!!

VBA Code:
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!!!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top