Copying a worksheet into the same workbook

[Michael]

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

 

[Ron de Bruin]

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

 

[Michael]

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

 

[Ron de Bruin]

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

 

[Ron de Bruin]

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

Use this instead

ws2.Name = ws1.Range("B3").Value

 

[Michael]

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

 

[Ron de Bruin]

Hi Michael

After you add the sheet you can add this

ActiveWindow.DisplayGridlines = False

 

[Michael]

That did the trick Ron, thanks so much for your help!

Mike

 

[Michael]

Hi Ron,

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

Thanks,
Mike

 

[Ron de Bruin]

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