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

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

NickHK

"Corey" <(E-Mail Removed)> wrote in message
news:%23RBG5%(E-Mail Removed)...
> 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....
>
>

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


"Corey" <(E-Mail Removed)> wrote in message news:%23RBG5%(E-Mail Removed)...
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....

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" wrote:

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

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

"Muhammed Rafeek M" <(E-Mail Removed)> wrote in
message news:235073CD-B407-460D-921E-(E-Mail Removed)...
> 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" wrote:
>
>> 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....
>>
>>
>>

strange.
Now graphs are working from command buttons.
Thanks

"Corey" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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....
>
> "Muhammed Rafeek M" <(E-Mail Removed)> wrote in
> message news:235073CD-B407-460D-921E-(E-Mail Removed)...
>> 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" wrote:
>>
>>> 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....
>>>
>>>
>>>
>
>

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

"Corey" wrote:

> strange.
> Now graphs are working from command buttons.
> Thanks
>
> "Corey" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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....
> >
> > "Muhammed Rafeek M" <(E-Mail Removed)> wrote in
> > message news:235073CD-B407-460D-921E-(E-Mail Removed)...
> >> 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" wrote:
> >>
> >>> 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....
> >>>
> >>>
> >>>
> >
> >
>
>
>