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

 

[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

 

[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]

Hi Dave,

Thank you!

---
Regards,
Norman

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

 

[Daniel]

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

 

[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.)

 

[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