Using a variable within a worksheet name

Question

I'm trying to loop through a set of worksheets to automate a copy and paste process in Excel VBA.

ThisWorkbook.Worksheets("Sheet" & counter).Activate throws an error.

The counter variable should run from 9 to 11.

I indicated the line that is giving me troubles with asterisks in a comment.

Sub copy_pm_forecast()

Dim counter, row, col, newRow, yearColBeg, yearColEnd As Integer
Dim foundRow, foundCol, foundYearBeg, foundYearEnd As Range
Dim currentSheet As String

counter = 9

Do
    currentSheet = "Sheet" & counter
    
    'this block activates the sheet that corresponds to the index indicated by variable "counter" and finds the correct row the "Actuals/COF" values
    
    ThisWorkbook.Worksheets(currentSheet).Activate '**

    Set foundRow = ActiveSheet.Range("A1:Z100").Find("Actuals/COF")
    row = foundRow.row
    
    'this block finds the columns in which the beginning month of the fiscal year and year total reside
    Set foundYearBeg = ActiveSheet.Range("A1:Z100").Find("July")
    yearColBeg = foundYearBeg.Column
    Set foundYearEnd = ActiveSheet.Range("A1:Z100").Find("TOTAL", MatchCase:=True)
    yearColEnd = foundYearEnd.Column
    
    'this block copies and pastes the values from row to row + 1
    With ThisWorkbook.Sheets(currentSheet)
        .Range(Cells(row, yearColBeg), Cells(row, yearColEnd)).Copy
        .Range(Cells(row + 1, yearColBeg), Cells(row + 1, yearColEnd)).PasteSpecial xlPasteValues
    End With
    
    'this block finds the proper column and row to which to paste the total values
    Set foundCol = ActiveSheet.Range("A1:Z100").Find("Prior COF")
    col = foundCol.Column
    newRow = foundCol.row
    
    'this block copies and pastes the values from the "Total" column to the "Prior COF" column
    With ThisWorkbook.Sheets(counter)
        .Range(Cells(newRow + 2, yearColEnd), Cells(row + 1, yearColEnd)).Copy
        .Range(Cells(newRow + 2, col), Cells(row + 1, col)).PasteSpecial xlPasteValues
    End With
    
    'increments "counter" by one so that the next worksheet is selected when the loop iterates again
    counter = counter + 1

Loop Until counter = 12

End Sub

I tried using the index number.

ThisWorkbook.Worksheets(counter).Activate

This works - however, the problem with just using the index is that I want to add or shuffle worksheets without any adverse affects to the output of the code.

I tried to isolate the problem by removing the variable.

ThisWorkbook.Worksheets("Sheet9").Activate

This is still throwing the error.

Answer 1

Here's one way to refer to a worksheet given the workbook and the sheet's codename:

Sub Tester()
    Dim ws As Worksheet
    Set ws = WorksheetSheetFromCodeName(ThisWorkbook, "Sheet" & "8")
    Debug.Print ws.Name, ws.CodeName
End Sub


Function WorksheetSheetFromCodeName(wb As Workbook, codeNm As String)
    Dim ws
    For Each ws In wb.Worksheets      'loop all worksheets in `wb`
        If ws.CodeName = codeNm Then  'got a match on CodeName?
            Set WorksheetSheetFromCodeName = ws  'return sheet reference
            Exit Function
        End If
    Next ws
End Function

Then inside your loop use (eg) ws.Range(...), ws.Cells(...) instead of just Range() or Cells()