PDA

View Full Version : Copy Sheet - Change Name with a twist.



whatsapro
06-17-2016, 01:45 PM
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
06-17-2016, 02:00 PM
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
06-17-2016, 02:30 PM
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
06-17-2016, 02:47 PM
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