Copy Sheet - Change Name with a twist.

[whatsapro]

Dim ws1 As Worksheet
            Set ws1 = ThisWorkbook.Worksheets("Funky")
            ws1.Copy ThisWorkbook.Sheets(Sheets.Count)

Ok so this does what I want it to do but I can't seem to figure out how to change the name of the copied sheet.

I can't use: ThisWorkbook.VBProject.VBComponents("Funky (2)".Name = "blah" either. I have no permission to use this and gives an error.

Is there a way to make exact copy with formatting while being able to change the sheet name?

[p45cal]

worksheet.Copy is a method that does not return an object so you have to rely on the copied sheet becoming the activesheet, so straight after the copy line have a line to 'capture' the active sheet by assigning an object variable to it with something along the lines of:
Set NewSht=ActiveSheet
Thereafter you can do what you will with it, such as:
NewSht.name = "blah"

It's very unlikely the user will manage to change the active sheet in the short time between the execution of the Copy and Set lines (in fact I doubt he could if he tried) but check out the vba Help on Application.Interactive and its attendant Remarks sectoin.

[whatsapro]

Thank you. Worked like a charm.

I hope soon I will be more of a contributor than leecher off this site. I need to sit down and learn the "real" way to do this rather than piece things together all the time from searches. This is kind of addicting. Thanks again.

[mdmackillop]

If you want user input, get if before the sheet is copied

Sub test()


Dim NewName As String
Dim ws1 As Worksheet


NewName = InputBox("New name")
Set ws1 = ThisWorkbook.Worksheets("Funky")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
ActiveSheet.Name = NewName
End Sub