Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Worksheet to last and rename with input box for value.
Maybe something like this will get you started:
Option Explicit Sub testme() With ActiveWorkbook .Worksheets("sheet1").Copy _ after:=.Sheets(.Sheets.Count) On Error Resume Next Do ActiveSheet.Name = InputBox(Prompt:="What name?") If Err.Number < 0 Then MsgBox "Can't use that name" & vbLf & "try again" Err.Clear Else Exit Do End If Loop On Error GoTo 0 End With End Sub (I copied sheet1 Josh wrote: How can I copy/paste a worksheet into it's current workbook as the last sheet and rename it with a pop-up input box requesting the name of the new worksheet. -- Dave Peterson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Worksheet to last and rename with input box for val
I'd use:
Option Explicit Sub testme() With ActiveWorkbook .Worksheets(.Worksheets.Count).Copy _ after:=.Sheets(.Sheets.Count) On Error Resume Next Do ActiveSheet.Name = InputBox(Prompt:="What name?") If Err.Number < 0 Then MsgBox "Can't use that name" & vbLf & "try again" Err.Clear Else Exit Do End If Loop On Error GoTo 0 End With End Sub Just in case you have some sheets that aren't worksheets (charts, old xl4 macro sheets,...) Josh wrote: Dave, Thank you, it is an enormous help, but the sheet that always gets copied is the last sheet when I open the workbook. So instead of .Worksheets("sheet1").Copy _ how can I make that the last sheet? The same as? .Worksheets(.Sheets.Count) ? -Josh "Dave Peterson" wrote: Maybe something like this will get you started: Option Explicit Sub testme() With ActiveWorkbook .Worksheets("sheet1").Copy _ after:=.Sheets(.Sheets.Count) On Error Resume Next Do ActiveSheet.Name = InputBox(Prompt:="What name?") If Err.Number < 0 Then MsgBox "Can't use that name" & vbLf & "try again" Err.Clear Else Exit Do End If Loop On Error GoTo 0 End With End Sub (I copied sheet1 Josh wrote: How can I copy/paste a worksheet into it's current workbook as the last sheet and rename it with a pop-up input box requesting the name of the new worksheet. -- Dave Peterson -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Worksheet to last and rename with input box for val
Once again thank you very much, I appreciate the help.
Sincerely Josh Hallenbeck "Dave Peterson" wrote: I'd use: Option Explicit Sub testme() With ActiveWorkbook .Worksheets(.Worksheets.Count).Copy _ after:=.Sheets(.Sheets.Count) On Error Resume Next Do ActiveSheet.Name = InputBox(Prompt:="What name?") If Err.Number < 0 Then MsgBox "Can't use that name" & vbLf & "try again" Err.Clear Else Exit Do End If Loop On Error GoTo 0 End With End Sub Just in case you have some sheets that aren't worksheets (charts, old xl4 macro sheets,...) Josh wrote: Dave, Thank you, it is an enormous help, but the sheet that always gets copied is the last sheet when I open the workbook. So instead of .Worksheets("sheet1").Copy _ how can I make that the last sheet? The same as? .Worksheets(.Sheets.Count) ? -Josh "Dave Peterson" wrote: Maybe something like this will get you started: Option Explicit Sub testme() With ActiveWorkbook .Worksheets("sheet1").Copy _ after:=.Sheets(.Sheets.Count) On Error Resume Next Do ActiveSheet.Name = InputBox(Prompt:="What name?") If Err.Number < 0 Then MsgBox "Can't use that name" & vbLf & "try again" Err.Clear Else Exit Do End If Loop On Error GoTo 0 End With End Sub (I copied sheet1 Josh wrote: How can I copy/paste a worksheet into it's current workbook as the last sheet and rename it with a pop-up input box requesting the name of the new worksheet. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro: Copy a worksheet and rename it to a specified value | Excel Discussion (Misc queries) | |||
Copy worksheet and rename sheet | Excel Discussion (Misc queries) | |||
Copy, Paste and Rename a Range using a macro | Excel Discussion (Misc queries) | |||
Rename a Worksheet on Input of Value in Cell | Excel Worksheet Functions | |||
Copy worksheet from multiple files in one DIR to another DIR & rename | Excel Programming |