Copying a worksheet into the same workbook

Michael · 01-31-2006, 11:50 AM

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 · 01-31-2006, 12:45 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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

Michael · 01-31-2006, 01:10 PM

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" <[email protected]> wrote in message news:[email protected]...
> > 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 · 01-31-2006, 01:35 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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 · 01-31-2006, 01:40 PM

> 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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> 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" <[email protected]> wrote in message news:[email protected]...
>>> > 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
>>>
>>>
>>>
>
>

Michael · 01-31-2006, 02:25 PM

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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> 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" <[email protected]> wrote in message news:[email protected]...
> >>> > 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 · 01-31-2006, 04:40 PM

Hi Michael

After you add the sheet you can add this

ActiveWindow.DisplayGridlines = False

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

"Michael" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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" <[email protected]> wrote in message news:[email protected]...
>> >> 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" <[email protected]> wrote in message news:[email protected]...
>> >>> > 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
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>>

Michael · 02-01-2006, 09:35 AM

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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> > 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" <[email protected]> wrote in message news:[email protected]...
> >> >> 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" <[email protected]> wrote in message news:[email protected]...
> >> >>> > 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
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Michael · 02-06-2006, 10:55 AM

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" <[email protected]> wrote in message news:[email protected]...
> > > 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" <[email protected]> wrote in message news:[email protected]...
> > >> > 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" <[email protected]> wrote in message news:[email protected]...
> > >> >> 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" <[email protected]> wrote in message news:[email protected]...
> > >> >>> > 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 · 02-06-2006, 04:40 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > > 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" <[email protected]> wrote in message news:[email protected]...
>> > >> > 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" <[email protected]> wrote in message news:[email protected]...
>> > >> >> 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" <[email protected]> wrote in message
>> > >> >>> news:[email protected]...
>> > >> >>> > 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
>> > >> >>>
>> > >> >>>
>> > >> >>>
>> > >> >
>> > >> >
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >