is there anyway to create a clone of an excel sheet in vba?

[Daniel]

is there anyway to create a clone of an excel sheet in vba?

[Norman Jones]

Hi Daniel,

To add a copy in the same workbook, try:

Sub Tester
With ActiveWorkbook
.Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
End With
End Sub

To create a new single-sheet workbook containing a copy of the sheet, try:

Sub Tester2
ActiveWorkbook.Sheets("MySheet").copy
End sub

---
Regards,
Norman



"Daniel" <[email protected]> wrote in message
news:[email protected]...
> is there anyway to create a clone of an excel sheet in vba?
>
>

[Robin Hammond]

Daniel,

Norman is right as usual, but just in case you meant workbook rather than
worksheet, have a look at the SaveCopyAs method in the vba help file.

Robin Hammond
www.enhanceddatasystems.com

"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> Hi Daniel,
>
> To add a copy in the same workbook, try:
>
> Sub Tester
> With ActiveWorkbook
> .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
> End With
> End Sub
>
> To create a new single-sheet workbook containing a copy of the sheet, try:
>
> Sub Tester2
> ActiveWorkbook.Sheets("MySheet").copy
> End sub
>
> ---
> Regards,
> Norman
>
>
>
> "Daniel" <[email protected]> wrote in message
> news:[email protected]...
>> is there anyway to create a clone of an excel sheet in vba?
>>
>>
>
>

[Dave Peterson]

Just a typo that won't matter if it's running against the activeworkbook, but
could matter if the workbook isn't active...

Sub Tester
With ActiveWorkbook
.Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
End With
End Sub

is missing a dot in front of sheets.count.

Sub Tester
With ActiveWorkbook
.Sheets("MySheet").Copy After:=.Sheets(.Sheets.Count)
End With
End Sub

Norman Jones wrote:
>
> Hi Daniel,
>
> To add a copy in the same workbook, try:
>
> Sub Tester
> With ActiveWorkbook
> .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
> End With
> End Sub
>
> To create a new single-sheet workbook containing a copy of the sheet, try:
>
> Sub Tester2
> ActiveWorkbook.Sheets("MySheet").copy
> End sub
>
> ---
> Regards,
> Norman
>
> "Daniel" <[email protected]> wrote in message
> news:[email protected]...
> > is there anyway to create a clone of an excel sheet in vba?
> >
> >

--

Dave Peterson

[Norman Jones]

Hi Dave,

Thank you!

---
Regards,
Norman



"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Just a typo that won't matter if it's running against the activeworkbook,
> but
> could matter if the workbook isn't active...
>
> Sub Tester
> With ActiveWorkbook
> .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
> End With
> End Sub
>
> is missing a dot in front of sheets.count.
>
> Sub Tester
> With ActiveWorkbook
> .Sheets("MySheet").Copy After:=.Sheets(.Sheets.Count)
> End With
> End Sub
>
> Norman Jones wrote:
>>
>> Hi Daniel,
>>
>> To add a copy in the same workbook, try:
>>
>> Sub Tester
>> With ActiveWorkbook
>> .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
>> End With
>> End Sub
>>
>> To create a new single-sheet workbook containing a copy of the sheet,
>> try:
>>
>> Sub Tester2
>> ActiveWorkbook.Sheets("MySheet").copy
>> End sub
>>
>> ---
>> Regards,
>> Norman
>>
>> "Daniel" <[email protected]> wrote in message
>> news:[email protected]...
>> > is there anyway to create a clone of an excel sheet in vba?
>> >
>> >
>
> --
>
> Dave Peterson

[Daniel]

in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to
the newly created copy of this sheet?

"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> Hi Daniel,
>
> To add a copy in the same workbook, try:
>
> Sub Tester
> With ActiveWorkbook
> .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
> End With
> End Sub
>
> To create a new single-sheet workbook containing a copy of the sheet, try:
>
> Sub Tester2
> ActiveWorkbook.Sheets("MySheet").copy
> End sub
>
> ---
> Regards,
> Norman
>
>
>
> "Daniel" <[email protected]> wrote in message
> news:[email protected]...
> > is there anyway to create a clone of an excel sheet in vba?
> >
> >
>
>

[Dave Peterson]

You have this at your other post:

dim newWks as worksheet

with activeworkbook
.sheets("mysheet).copy _
before:=.sheets(1)
end with
set newwks = activesheet
newwks.name = "this is a new sheet!"

(the activesheet is the one that just got created.)

Daniel wrote:
>
> in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to
> the newly created copy of this sheet?
>
> "Norman Jones" <[email protected]> wrote in message
> news:%[email protected]...
> > Hi Daniel,
> >
> > To add a copy in the same workbook, try:
> >
> > Sub Tester
> > With ActiveWorkbook
> > .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
> > End With
> > End Sub
> >
> > To create a new single-sheet workbook containing a copy of the sheet, try:
> >
> > Sub Tester2
> > ActiveWorkbook.Sheets("MySheet").copy
> > End sub
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Daniel" <[email protected]> wrote in message
> > news:[email protected]...
> > > is there anyway to create a clone of an excel sheet in vba?
> > >
> > >
> >
> >

--

Dave Peterson

[Norman Jones]

Hi Daniel,

Try something like:

Dim WB As Workbook
Dim WS As Worksheet

Set WB = ActiveWorkbook
With WB
.Sheets("Mysheet").Copy Before:=.Sheets(1)
End With

Set WS = ActiveSheet

---
Regards,
Norman



"Daniel" <[email protected]> wrote in message
news:[email protected]...
> in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference
> to
> the newly created copy of this sheet?
>
> "Norman Jones" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi Daniel,
>>
>> To add a copy in the same workbook, try:
>>
>> Sub Tester
>> With ActiveWorkbook
>> .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count)
>> End With
>> End Sub
>>
>> To create a new single-sheet workbook containing a copy of the sheet,
>> try:
>>
>> Sub Tester2
>> ActiveWorkbook.Sheets("MySheet").copy
>> End sub
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Daniel" <[email protected]> wrote in message
>> news:[email protected]...
>> > is there anyway to create a clone of an excel sheet in vba?
>> >
>> >
>>
>>
>
>