VBA won't loop through sheets - Error 1004 on "Range"

[GazNicki]

Hi all

I am trying to build a VBA code that will clear the contents of select cells in all the sheets in my workbook, with the exception of one sheet.

The workbook has 54 sheets. The first sheet is named "Intro" and the remaining sheets are name 1 to 53, representing weeks.

The cells of these sheets I want to clear are, C5:G46.

I want to clear the contents of each sheet, activate cell C5 as the active cell, then move on the the next until the workbook is clear.

Ultimately, I want to be able to:

• Save the file as a new file with a new filename (The year, plus one - so if the sheet is 2022.xlsm, then the new sheet is 2023.xlsm)
• Change cell C2 on the Intro sheet to the next year (so if it says 2022, then it is changed to 2023)
• Clear the contents of the cell ranges in the remaining sheets
• Save the document to apply the changes

This is my end goal, and I am trying to learn how to code the VBA for these. The Loop seems to be the most difficult so far.

Below is my code. The error I get is a 1004 for the "Range" part of the code.

Please can you help?

Private Sub CommandButton1_Click()
' SelectCellsClear Macro
' Selects a range of cells in all sheets except Intro & Settings, then clears their contents
Dim a As Integer
    numsheets = Application.Worksheets.Count 'Count the number of worksheets in the workbook and assign this to the integer

For ws = 1 To numsheets                     'Defines ws as a counter starting at 1 and ending o nthe number of sheets

Worksheets(ws).Activate                      'Makes the sheet active

    If ActiveSheet.Name <> "Intro" Then     'This sheet is ignored from the loop
        Range("C5:G46").Select              'This the range of cells to selected
        Selection.Clear                     'Clear the contents of the selectd cells
        Range("B2").Select                  'Make cell C5 the active cell before moving on
    End If
    
    Next                                    'Repeat this for all the sheets
    
    Worksheets("Intro").Activate            'Go back to the first sheet
    Worksheets("Intro").Cells(1, 1).Select  'Make cell A1 the active cell
End Sub

 

[DanteAmor]

The code works for me.
Run the code by pressing the F8 key, i.e. step by step. Check which sheet the macro stops on.
And check what is different about that sheet with respect to the others.
Or if the problem occurs from the first page.
Do you have protected or hidden sheets?

Or try this:

Private Sub CommandButton1_Click()
  Dim ws As Worksheet
  
  Application.ScreenUpdating = False
  For Each ws In Sheets
  
    If ws.Name <> "Intro" Then    'This sheet is ignored from the loop
      ws.Select
      Range("C5:G46").Clear       'Clear the contents of the selectd cells
      Range("B2").Select          'Make cell C5 the active cell before moving on
    End If
    
  Next                                    'Repeat this for all the sheets
  
  Worksheets("Intro").Activate            'Go back to the first sheet
  Worksheets("Intro").Cells(1, 1).Select  'Make cell A1 the active cell
  Application.ScreenUpdating = True
End Sub

 

[GazNicki]

Thanks

There are no hidden sheets or cells, and each of the sheets numbered 1 - 53 are exact copies.

Both your code and my code fails at the point where Range.Select is used.

I have commented out the Range.Select on both of our codes, and the code works but only on the Intro sheet. It doesn't touch the other sheets. Its so odd.

 

[DanteAmor]

I have commented out the Range.Select on both of our codes, and the code works but only on the Intro sheet. It doesn't touch the other sheets. Its so odd.

That's pretty weird. The 2 macros work for me.
Do you have merged cells?

Could you share your file?
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[RoryA]

Your code is in a worksheet code module, so unqualified references to Range refer to that sheet, not the active one. YOu can use:

Private Sub CommandButton1_Click()
' SelectCellsClear Macro
' Selects a range of cells in all sheets except Intro & Settings, then clears their contents
Dim a As Integer
    numsheets = Application.Worksheets.Count 'Count the number of worksheets in the workbook and assign this to the integer

For ws = 1 To numsheets                     'Defines ws as a counter starting at 1 and ending o nthe number of sheets

With Worksheets(ws)
   .Activate                      'Makes the sheet active

    If .Name <> "Intro" Then     'This sheet is ignored from the loop
        .Range("C5:G46").Clear                     'Clear the contents of the specified cells
        .Range("B2").Select                  'Make cell C5 the active cell before moving on
    End If
    End With
    Next                                    'Repeat this for all the sheets
    
    Worksheets("Intro").Activate            'Go back to the first sheet
    Worksheets("Intro").Cells(1, 1).Select  'Make cell A1 the active cell
End Sub

 

[GazNicki]

I have tried other options over the course of the afternoon, and got to an array which works. The code is below:

Sub NewYear()
' Setup the document as a new year, clear and ready to go'

' Let's check that the user wants to do this first
If MsgBox("This will setup a fresh, clean file. Are you sure?", vbYesNo) = vbNo Then Exit Sub

'They're happy. Lets proceed

'Set the variables we need
Dim PATH As String
Dim FILENAME As String
Dim YEAR As String
Dim WS As Integer

'This section defines the contents of the variables, and saves a new version of the file.
PATH = ActiveWorkbook.PATH & "\"
FILENAME = Range("F2") & ".xlsm"
ActiveWorkbook.SaveAs FILENAME:=PATH & FILENAME, FileFormat:=52

'This section changes the current year on the Intro page to the year of the file
YEAR = Range("F2")
Range("C2").Value = YEAR

'Let's tell the user what we have done, and where they can find it.
MsgBox "ROBOT says," & vbCrLf & "" & vbCrLf & _
"I have saved the new file for you. I is called:" & vbCrLf & "" & vbCrLf & _
FILENAME & vbCrLf & "" & vbCrLf & _
"Located in following location:" & vbCrLf & "" & vbCrLf & _
PATH
   
'Let's clear the Weekly Sheets now
    numsheets = Application.Worksheets.Count
    
    For WS = 1 To numsheets
    
    With Worksheets(WS)
        .Activate
        
        If .Name <> "Intro" Then
            .Range("C5:G46").ClearContents
            .Range("C5").Select
        End If
        End With
        Next
 
        MsgBox "ROBOT says," & vbCrLf & "" & vbCrLf & _
            "I have cleared all the weekly sheets for you now."
    
    Sheets("Intro").Select
    Range("C2").Select
    ActiveWorkbook.Save
    
    MsgBox "Robot Says" & vbCrLf & "" & vbCrLf & _
            "The sheet is now done. You are working on the new file now. You can close this if you want to."
End Sub

However, I will try to use the loop as posted above too, as this is a much tidier piece of code.

*EDIT* - Updated the code with the working Loop from RoryA. Many thanks.

 

[rlv01]

One more.

Private Sub CommandButton1_Click()
    Dim WS As Worksheet, FinalWS As Worksheet
    
    For Each WS In ThisWorkbook.Worksheets
        WS.Activate
        Select Case WS.Name
            Case "Intro"
                WS.Range("A1").Select                   'Make cell A1 the active cell
                Set FinalWS = WS
            Case Else
                WS.Range("C5:G46").Clear                'Clear the contents of the range of cells
                WS.Range("B2").Select                   'Make cell C5 the active cell before moving on
        End Select
    Next WS
    FinalWS.Activate                                    'Go back to the first sheet
End Sub