I am trying to create a Macro that will allow me to copy a specific workbook, and rename it.
I have the following code:
Sub NewSheet()
Dim origSht As Worksheet
Dim destSht As Worksheet
On Error GoTo eHandle
Set origSht = Sheet3
Sheets.Add.Name = InputBox("What Would You Like to Call the New Sheet?")
Set destSht = ActiveSheet
origSht.Cells.Copy Destination:=destSht.Cells
Exit Sub
eHandle:
MsgBox "You must name the new sheet"
Set origSht = Nothing
Set destSht = Nothing
End Sub
So this copies Sheet3, but strips out all of the formulas and resizes all of the columns. Can anyone suggest why it does that, and what needs changing to stop this happening?
ALSO...... am I right in thinking that Sheet3 will always be Sheet3 so i don't need to worry about the code falling over in the future?
Bookmarks