VBA - Unable to Set Worksheet Variable

user_14556148 0
Viewed 274

I'm new to VBA but I'm not new to scripting languages. I have a problem with setting a worksheet variable for some reason in this routine. The same syntax works in other routines but for some reason it won't in this one.

Can someone explain why my worksheet variable 'wks' will NOT populate? I'm not getting any errors but it will not populate. It remains empty.

The problem is this line "Set wks = .Sheets(strTemplate)". The variable 'strTemplate', when mousing over it DOES indicate the proper template sheet name but the worksheet variable 'wks' never populates.

Here's the subroutine that creates a copy of a template sheet, then renames it in order to be populated with data from the 'Main' sheet. I even put in 'Debug' commands but the one that prints "Sheet =" never executes due to 'wks' being empty.

' REPLACE MAIN WORKSHEET AFTER COPYING
'
Public Sub SheetReplace(strSheetName As String, strTemplate As String)
    Dim wks As Worksheet

Debug.Print "Entered SheetReplace"
    ' We don't want screen updating
    Application.ScreenUpdating = False

    ' Delete the existing Main - Copy if it exists
    Application.DisplayAlerts = False   'turn off alerts
    On Error Resume Next                'continue execution
    Worksheets("Main - Copy").Delete    'delete the worksheet
    Err.Clear                           'clear error
    Application.DisplayAlerts = True    'turn on alerts

    With ThisWorkbook
        ' Duplicate the Main - Tmplt sheet
        ' Duplicate the template sheet
        Set wks = .Sheets(strTemplate)
Debug.Print "Sheet = [" & wks & "]"

        ' Check sheet visibility
        isVisible = (wks.Visible = xlSheetVisible)
    
        ' Make the sheet visible if not
        If Not isVisible Then wks.Visible = xlSheetVisible
    
        ' Copy duplicate to the end of the sheets
        wks.Copy After:=.Sheets(strSheetName)
    
        ' Change the name of the sheet
        ActiveSheet.Name = strSheetName & " - Copy"
    
        ' Make the sheet invisible
        If isVisible Then ws.Visible = xlSheetHidden
    
        ' BEGIN COPYING MAIN SHEET INFO
        With Worksheets("Main")
            Set srcWorkSheet = Worksheets(ActiveSheet.Name)         ' Duplicate the Copy name
            lastRowMain = .Cells(.Rows.Count, "A").End(xlUp).Row    ' Find the last row used in "Main" sheet
            ' Copy the ranges that contain daily data.
            ' Copy the Month
            .Range("$C$8").Copy Destination:=Worksheets(ActiveSheet.Name).Range("$C$8")
            .Range("$I$11").Copy Destination:=Worksheets(ActiveSheet.Name).Range("$I$11")
            .Range("$B15:$I51").Copy Destination:=Worksheets(ActiveSheet.Name).Range("$B15:$I51")
            srcWorkSheet.Visible = xlSheetHidden                    ' Make the copy sheet invisible
    
            ' Clear cells (including formatting)
            .Range("$C15:$H51").ClearContents
        End With
        ' THIS IS THE END OF THE MAIN COPY
    End With
End Sub

Any info would be appreciated. Thanks.

vba excel
2 Answers

Well apparently I had a syntax error & the 'wks' variable WAS being set, I just needed to reference the 'wks.Name' property. Once I did that the execution continued.

Set wks = .Sheets(strTemplate)
Debug.Print "Sheet = [" & wks.Name & "]" <------ OUTPUT: Sheet = [Main - Copy]

@VBasic2008 : Yep. I didn't see your post until now but thanks for responding with that. You've all been a great help.

user_145561480

Update Workbook

  • Use Option Explicit.
  • Use ThisWorkbook if you're doing stuff in the workbook containing the code.
  • On Error Goto 0 turns off error handling (This was the fatal mistake).

A Quick Fix

Option Explicit

Public Sub SheetReplace(strSheetName As String, strTemplate As String)
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    ' We don't want screen updating
    Application.ScreenUpdating = False

    ' Delete the existing Main - Copy if it exists
    On Error Resume Next                'continue execution
    Application.DisplayAlerts = False   'turn off alerts
    wb.Worksheets("Main - Copy").Delete    'delete the worksheet
    Application.DisplayAlerts = True    'turn on alerts
    On Error GoTo 0                     'disable error handling

    ' Duplicate the Main - Tmplt sheet
    ' Duplicate the template sheet
    Dim wks As Worksheet
    Set wks = wb.Worksheets(strTemplate)

    ' Check sheet visibility
    Dim isVisible As Boolean
    isVisible = (wks.Visible = xlSheetVisible)

    ' Make the sheet visible if not
    If Not isVisible Then
        wks.Visible = xlSheetVisible
    End If
    
    ' Copy duplicate to the end of the sheets
    wks.Copy After:=wb.Worksheets(strSheetName)
    
    ' Change the name of the sheet
    Dim src As Worksheet
    Set src = wb.ActiveSheet
    src.Name = strSheetName & " - Copy"
    
    ' Make the sheet invisible
    If isVisible Then wks.Visible = xlSheetHidden
    
    ' BEGIN COPYING MAIN SHEET INFO
    Dim LastRowMain As Long
    With wb.Worksheets("Main")
        ' Find the last row used in "Main" sheet
        LastRowMain = .Cells(.Rows.Count, "A").End(xlUp).Row
        ' Copy the ranges that contain daily data.
        ' Copy the Month
        .Range("$C$8").Copy Destination:=src.Range("$C$8")
        .Range("$I$11").Copy Destination:=src.Range("$I$11")
        .Range("$B15:$I51").Copy Destination:=src.Range("$B15:$I51")
        
        ' If you only need values, this is more efficient (faster).
        'src.Range("$C$8").Value = .Range("$C$8").Value
        'src.Range("$I$11").Value = .Range("$I$11").Value
        'src.Range("$B15:$I51").Value = .Range("$B15:$I51").Value
        
        ' Make the copy sheet invisible
        src.Visible = xlSheetHidden
        ' Clear cells (including formatting)
        .Range("$C15:$H51").ClearContents
    End With
        
    ' THIS IS THE END OF THE MAIN COPY

End Sub
user_98140690
Related