Loop through specific worksheets in a collection

VBA-Apprentice

New Member
Joined
Sep 13, 2017
Messages
3
Hi All,

I am working on a VBA code that would loop through collection of worksheets (~40 sheets) in a workbook and would specifically pick and copy cells which are coloured in a particular worksheet and then paste the copied range on a separate sheet in the same workbook. I tried to nest a For Next loop (to loop through sheets) with the loop which you have shown in your video. Now the problem here is the loop is working fine within a given sheet i.e. it is selecting or copying a coloured range and moving on to a next coloured range perfectly, but then it is not looping to next sheet in the collection which also has coloured ranges to be copied.


Can someone please help me on this?

Here is the code that I've written:

Sub loopthroughsheets()


Dim WS_Count As Integer
Dim irow As Long
Dim lr As Long ' last row
Dim n As Long ' loopcounter for looping to next sheet
Dim i As Long ' loopcounter for looping cells in a sheet




WS_Count = ActiveWorkbook.Worksheets.<wbr>Count

For n = 6 To WS_Count
lr = Worksheets(n).Cells(Rows.<wbr>Count, 1).End(xlUp).Row
If 2 > lr Then Exit Sub
irow = 2

For i = 2 To lr
If Cells(i, 1).Interior.Color <> 16777215 Then
Cells(i, 1).Resize(1, 7).Select
End If
Next i
Next n


End Sub

 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Try activating the worksheet

Code:
Sub loopthroughsheets()
Dim WS_Count As Integer
Dim irow As Long
Dim lr As Long ' last row
Dim n As Integer ' loopcounter for looping to next sheet
Dim i As Long ' loopcounter for looping cells in a sheet
WS_Count = ActiveWorkbook.Worksheets.Count

For n = 1 To WS_Count
[color=red]Worksheets(n).Activate[/color]
lr = Worksheets(n).Cells(Rows.Count, 1).End(xlUp).Row
If 2 > lr Then Exit Sub
irow = 2

For i = 2 To lr
If Cells(i, 1).Interior.Color <> 16777215 Then
Cells(i, 1).Resize(1, 7).Select
End If
Next i
Next n


End Sub
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

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