(Solved) – How to set a cell value as a reference variable to a sheet name

  • by
(solved)-–-how-to-set-a-cell-value-as-a-reference-variable-to-a-sheet-name

I am trying to use a cell value to create a sheet using a variable and then use the same variable to create a worksheet variable for further code changes. I get the error:

Object Variable or With Block Variable Not set

 Sub Shift_New_Sheet()
    Dim tmpSheetName As String
    Dim SheetName As String
    Dim ws As Worksheet
    Dim Sheet1 As Worksheet
    Dim Sheet2 As Worksheet
    Dim Sheet3 As Worksheet

    tempSheetName = Sheet1.Cells(8, 3).Value
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = tempSheetName

    Set Sheet1 = Sheets("Input")
    Set Sheet2 = Sheets("Mastersheet")
    Set Sheet3 = Sheets(tempSheetName)  'This assignment is where I get error
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *