Copying a worksheet into the same workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, and thanks in advane for the help!

I would like to know how to copy an existing worksheet and automatically
rename it from a cell within that sheet into the same workbook. I have
searched but not found what I want to do. I think it has to do with the
newsheet event but not sure how to do it.

Mike
 
Try this for the activesheet with the sheet name in B3

Sub test()
ActiveSheet.Copy after:=Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = Range("B3").Value
On Error GoTo 0
End Sub
 
Thanks so much Ron! It worked great!

I found that I only want to copy a range of cells into the new worksheet
because I don't want the macro buttons copied into the new sheet. Any
suggestions?

Mike
 
Two ways

This also delete all shapes on the new sheet

Sub test()
ActiveSheet.Copy after:=Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = Range("B3").Value
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
End Sub


Or copy a range into a new worksheet

Sub test2()
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = ActiveSheet
Set ws2 = Worksheets.Add(after:=Sheets(Sheets.Count))

On Error Resume Next
ActiveSheet.Name = ws1.Range("B3").Value
On Error GoTo 0

ws1.Range("A1:G20").Copy ws2.Range("A1")

Set ws1 = Nothing
Set ws2 = Nothing

End Sub
 
ActiveSheet.Name = ws1.Range("B3").Value

Use this instead

ws2.Name = ws1.Range("B3").Value
 
All has worked great so far, many thanks.

One last question (I hope): How do I get the new sheet not to display the
gridlines automatically? The sheet it is copied from has the gridlines
turned off.

Mike
 
Hi Michael

After you add the sheet you can add this

ActiveWindow.DisplayGridlines = False
 
Hi Ron,

Is it possible to copy the sheet's formatting and print area set-ups also?

Thanks,
Mike
 
Hi

If you want that it is better to copy the whole sheet and delete what you not want.
Like I show you in this macro that delete the shapes on the new sheet.

Sub test()
ActiveSheet.Copy after:=Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = Range("B3").Value
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
End Sub
 
Back
Top