copy sheet complete with command buttons and code into other workbook !!!

[Corey]

I have code that copies the range A1:AB43 from a sheet in say workbook 1
into workbook 2.
But i have just realised that the command buttons and the code in that sheet
are not being pasted , but only tyhe values.
I am using this code to copy paste :

Workbooks("Workbook1").Activate
Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("SheetA")
.Select
.Range("a1:ab43").Copy ' <============ Does this copy also the button
and code ??
Workbooks("Workbook2").Activate
Sheets.Add
Sheets("Sheet1").Select
Columns("A:B").Select
Selection.ColumnWidth = 10
Columns("C:AA").Select
Selection.ColumnWidth = 5
ActiveSheet.Name = "SheetA"
Sheets("SheetA").Range("A1:AB43").PasteSpecial xlPasteAll ' <===== This
is not pasting the Buttons and code also
Range("A1").Select
End With

Corey....

 

[NickHK]

Corey,
You need to copy the whole sheet, not just a range.
Record a macro to get the code.

NickHK

 

[Jim Cone]

Corey,
Looks like buttons from the Control Toolbox don't get copied.
You can use buttons from the Forms toolbar without a problem.
Another way to go is to copy the sheet and then delete what you
don't want from the new sheet...
Worksheets("SheetA").Copy after := Worksheets("SheetC")
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


I have code that copies the range A1:AB43 from a sheet in say workbook 1
into workbook 2.
But i have just realised that the command buttons and the code in that sheet
are not being pasted , but only tyhe values.
I am using this code to copy paste :

Workbooks("Workbook1").Activate
Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("SheetA")
.Select
.Range("a1:ab43").Copy ' <============ Does this copy also the button
and code ??
Workbooks("Workbook2").Activate
Sheets.Add
Sheets("Sheet1").Select
Columns("A:B").Select
Selection.ColumnWidth = 10
Columns("C:AA").Select
Selection.ColumnWidth = 5
ActiveSheet.Name = "SheetA"
Sheets("SheetA").Range("A1:AB43").PasteSpecial xlPasteAll ' <===== This
is not pasting the Buttons and code also
Range("A1").Select
End With

Corey....

 

[Muhammed Rafeek M]

Pls try this one

Sub SheetMove()
Dim wbD As Workbook
Dim wbT As Workbook
Set wbD = Workbooks(ThisWorkbook.Name) 'Source workbook name
Set wbT = Workbooks("sample") ' Destination workbook name
wbD.Sheets(1).Copy before:=wbT.Sheets(1)
End Sub

don't forget me rate

 

[Corey]

Ok,
Tried the Macro Recorder to no avail.
I have limited success with your code Muhammed. Thanks.
I can get the buttons and code pasted into the new workbook now, but,

The buttons that actaull create graphs return an error now:
Object variable or with block variable not set ERROR.
Code below:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub CommandButton21_Click()
Application.ScreenUpdating = False
'
' Macro99 Macro
' Macro recorded 4/10/2006 by Corey
'

'
Charts.Add
ActiveChart.ChartType = xl3DColumn
ActiveChart.SetSourceData Source:=Sheets("Utilization
Sheet").Range("T41:T42" _
), PlotBy:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "='Utilization Sheet'!R41C25"
ActiveChart.SeriesCollection(2).Name = "='Utilization Sheet'!R2C25"
ActiveChart.SeriesCollection(1).Values = "='Utilization Sheet'!R41C24"
ActiveChart.SeriesCollection(1).Name = "='Utilization Sheet'!R2C24"
ActiveChart.SeriesCollection(3).Values = "='Utilization Sheet'!R41C26"
ActiveChart.SeriesCollection(3).Name = "='Utilization Sheet'!R2C26"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Utilization
Sheet"
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.HasDataTable = False
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(2).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(3).Select
Selection.BarShape = xlCylinder
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.PlotArea.Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "ALL Employee's"
.Elevation = 15
.Perspective = 30
.Rotation = 40
.RightAngleAxes = False
.HeightPercent = 100
.AutoScaling = True
.ChartArea.Select
End With
Range("a1").Select
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The new sheet is named "Utilization Sheet" but i cannot find why i get the
error???

Any ideas ??

Corey....

 

[Corey]

strange.
Now graphs are working from command buttons.
Thanks

 

[JLGWhiz]

I think you have to save it after you paste it to initialize the code.