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

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

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