Copying a worksheet into the same workbook
 

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

Copying a worksheet into the same workbook
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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

Copying a worksheet into the same workbook
 

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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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

Copying a worksheet into the same workbook
 

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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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

Copying a worksheet into the same workbook
 

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

Use this instead

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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

Copying a worksheet into the same workbook
 

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" wrote:

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

Use this instead

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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

Copying a worksheet into the same workbook
 

Hi Michael

After you add the sheet you can add this

ActiveWindow.DisplayGridlines = False


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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" wrote:

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

Use this instead

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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

Copying a worksheet into the same workbook
 

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

Mike

"Ron de Bruin" wrote:

Hi Michael

After you add the sheet you can add this

ActiveWindow.DisplayGridlines = False


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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" wrote:

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

Use this instead

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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

Copying a worksheet into the same workbook
 

Hi Ron,

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

Thanks,
Mike

"Michael" wrote:

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

Mike

"Ron de Bruin" wrote:

Hi Michael

After you add the sheet you can add this

ActiveWindow.DisplayGridlines = False


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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" wrote:

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

Use this instead

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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

Copying a worksheet into the same workbook
 

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





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
Hi Ron,

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

Thanks,
Mike

"Michael" wrote:

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

Mike

"Ron de Bruin" wrote:

Hi Michael

After you add the sheet you can add this

ActiveWindow.DisplayGridlines = False


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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" wrote:

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

Use this instead

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael" wrote in message
...
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