Hi Everyone,
I am new to VBA, and I figured that trying to code is the best way to code. Anyway, I am trying to code a macro that will delete all worksheets in a workbook, unless that worksheet is named "Sheet1". In which case, I want the user to input what "Sheet1" should be named.
I am getting the run-time error "Object variable or With block variable not set". Here is the current code:
Can anyone tell me what I am doing wrong here? The example on MSDN website didn't use "Set" when defining a variable. Thanks for the help everyone!
-Steven
I am new to VBA, and I figured that trying to code is the best way to code. Anyway, I am trying to code a macro that will delete all worksheets in a workbook, unless that worksheet is named "Sheet1". In which case, I want the user to input what "Sheet1" should be named.
I am getting the run-time error "Object variable or With block variable not set". Here is the current code:
Code:
Sub RanameSheet1()
'
' RanameSheet1 Macro
' Deletes ALL worksheets except Sheet1. Renames Sheet1 to user input.
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Dim myWorksheet As Worksheet
For Each myWorksheet In Worksheets
If myWorksheet.Name <> "Sheet1" Then
myWorksheet.Delete
End If
Next
If myWorksheet.Name = "Sheet1" Then
myInput = InputBox("Rename Worksheet 1 to:")
myWorksheet.Name = myInput
End If
ActiveWorkbook.Save
Run ("Workbook Saved")
End Sub
Can anyone tell me what I am doing wrong here? The example on MSDN website didn't use "Set" when defining a variable. Thanks for the help everyone!
-Steven