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

  • Thread starter Thread starter Daniel
  • Start date Start date
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
 
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
 
Hi Dave,

Thank you!

---
Regards,
Norman



Dave Peterson said:
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
Click to expand...
 
in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to
the newly created copy of this sheet?
 
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.)
 
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
 
Back
Top