I am putting together a macro that
1) Creates a new tab as a copy of a tab named 'Template'
2) Queries the user to enter a name for the new tab
3) Inserts a new line to a table within a worksheet in a tab named 'Summary'
4) Enters the new tab name into the cell in the first column of that new line
5) Then makes that name a hyperlink to the new tab by that name.
Once the new tab is created named, the hyperlink I need shows up as the name entered with !Print_Area appended (e.g., tabname!Print_Area).
I tried the do that in the code below, but get Run-time error'91' Object variable or With Block variable not set.
I've tried a bunch of different ways to write it, but just can't figure it out. The key is in the last line - the SubAddress part. I think I need to set the wks of wks.Name to the correct value, but am not sure what to do.
Ultimately, I also need to figure out how to select the first row that has nothing in it, and then do my insert. As it is now, once I run the macro once, line 21 will now be line 22 and the macro will insert the new line in the wrong place. I have sorting macros that have a range, but that range doesn't extend when I insert a new row, so I need to change those macros to 'look' for the first blank row.
Sorry if this is too much, but any help would be much appreciated. I am just beginning to use/learn vb/macros and don't know what I am doing!
Sub InsertOpportunity()
'
' InsertOpportunity Macro
'
'
Dim sName As String
Dim wks As Worksheet
Worksheets("Template").Copy after:=Sheets("Active -->")
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Loop
Set wks = Nothing
Sheets("Summary").Select
Rows("21:21").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A21").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & wks.Name & "'!Print_Area", TextToDisplay:=sName
End Sub
1) Creates a new tab as a copy of a tab named 'Template'
2) Queries the user to enter a name for the new tab
3) Inserts a new line to a table within a worksheet in a tab named 'Summary'
4) Enters the new tab name into the cell in the first column of that new line
5) Then makes that name a hyperlink to the new tab by that name.
Once the new tab is created named, the hyperlink I need shows up as the name entered with !Print_Area appended (e.g., tabname!Print_Area).
I tried the do that in the code below, but get Run-time error'91' Object variable or With Block variable not set.
I've tried a bunch of different ways to write it, but just can't figure it out. The key is in the last line - the SubAddress part. I think I need to set the wks of wks.Name to the correct value, but am not sure what to do.
Ultimately, I also need to figure out how to select the first row that has nothing in it, and then do my insert. As it is now, once I run the macro once, line 21 will now be line 22 and the macro will insert the new line in the wrong place. I have sorting macros that have a range, but that range doesn't extend when I insert a new row, so I need to change those macros to 'look' for the first blank row.
Sorry if this is too much, but any help would be much appreciated. I am just beginning to use/learn vb/macros and don't know what I am doing!
Sub InsertOpportunity()
'
' InsertOpportunity Macro
'
'
Dim sName As String
Dim wks As Worksheet
Worksheets("Template").Copy after:=Sheets("Active -->")
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Loop
Set wks = Nothing
Sheets("Summary").Select
Rows("21:21").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A21").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & wks.Name & "'!Print_Area", TextToDisplay:=sName
End Sub
