VBA loop to exclude specific worksheets

edwardl96

New Member
Joined
May 15, 2014
Messages
23
Hello,
I was hoping someone could help me?
I am trying to run a basic loop over all sheets but two - sheet 1 and sheet 33 - the below is what I have - it loops over all sheets and doesn't skip 1 and 33.

VBA Code:
Sub restall_but()

Dim rs As Worksheet

For Each rs In ThisWorkbook.Worksheets
   If rs.Name <> "UK pick up" And rs.Name <> "Template" Then
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "='UK pick up'!R[4]C[12]"
    Range("C4").Select
    Selection.AutoFill Destination:=Range("C4:C12"), Type:=xlFillDefault
    Range("C4:C12").Select
    Range("C9").Select
    Selection.ClearContents
    Range("L4").Select
    ActiveCell.FormulaR1C1 = "='UK pick up'!R[15]C[3]"
    Range("L4").Select
    Selection.AutoFill Destination:=Range("L4:L12"), Type:=xlFillDefault
    Range("L4:L12").Select
    Range("L9").Select
    Selection.ClearContents
    Range("L12").Select
    ActiveCell.FormulaR1C1 = "=TEXT('UK pick up'!R[15]C[3],""dd-mm-yyyy"")"
   End If
Next rs

End Sub

many thanks in advance.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
You need to activate the worksheet after your original if statement too, i,e,
rs.Activate
Otherwise, you are never moving off of the original worksheet.

You can also simplify your code a bit by getting rid of most of your Selection/ActiveCell combinations. They usually aren't necessary.
Most literal couplets that are the result of the literal Macro Recorder like this:
VBA Code:
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "='UK pick up'!R[4]C[12]"
cam be simplified to this:
VBA Code:
    Range("C4").FormulaR1C1 = "='UK pick up'!R[4]C[12]"
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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