ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Error in PasteSpecial ? (https://www.excelbanter.com/excel-programming/376241-error-pastespecial.html)

Corey
Error in PasteSpecial ?
 
=====================================
Sub TimeSheets()
Sheets("AAA").Range("A1:U41").Insert
With ActiveWorkbook
Sheets("ZZZ").Select
End With
With activeworksheet
Range("a1:u41").Copy
Sheets("AAA").Select
'Worksheets("AAA").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Range("A1").PasteSpecial <============== ERROR
End With
End Sub
=====================================

I am using the above code to copy and paste values froM another worksheet.
I WANT THE FORMAT AND VALUES COPIED, which the abovew does, however,
I get a "Method of 'PasteSpecial' of Object 'Range' Failed error".
If i click end ALL is fine, how can i rid the code of the error alert?


Corey



Ken Puls
Error in PasteSpecial ?
 
You haven't given the pastespecial method enough arguments. It should
be something like:

Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

or

Range("A1").PasteSpecial Paste:=xlPasteAll

or one of the other constants that should expose itself to you via
intellisense. To see them all, type in the xlPaste portion, and then
hit Ctrl+Spacebar. The Intellisense will give you the listing of
constants starting at the Paste portion. Scroll through them to see
which you're after.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

Corey wrote:
=====================================
Sub TimeSheets()
Sheets("AAA").Range("A1:U41").Insert
With ActiveWorkbook
Sheets("ZZZ").Select
End With
With activeworksheet
Range("a1:u41").Copy
Sheets("AAA").Select
'Worksheets("AAA").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Range("A1").PasteSpecial <============== ERROR
End With
End Sub
=====================================

I am using the above code to copy and paste values froM another worksheet.
I WANT THE FORMAT AND VALUES COPIED, which the abovew does, however,
I get a "Method of 'PasteSpecial' of Object 'Range' Failed error".
If i click end ALL is fine, how can i rid the code of the error alert?


Corey



Corey
Error in PasteSpecial ?
 
Thanks for the reply.
I am trying the :

Range("A1").PasteSpecial Paste:=xlPasteAll

code but i still seems to get the same error.???

--
Regards

Corey


"Ken Puls" wrote in message
.. .
You haven't given the pastespecial method enough arguments. It should be
something like:

Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

or

Range("A1").PasteSpecial Paste:=xlPasteAll

or one of the other constants that should expose itself to you via
intellisense. To see them all, type in the xlPaste portion, and then hit
Ctrl+Spacebar. The Intellisense will give you the listing of constants
starting at the Paste portion. Scroll through them to see which you're
after.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

Corey wrote:
=====================================
Sub TimeSheets()
Sheets("AAA").Range("A1:U41").Insert
With ActiveWorkbook
Sheets("ZZZ").Select
End With
With activeworksheet
Range("a1:u41").Copy
Sheets("AAA").Select
'Worksheets("AAA").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Range("A1").PasteSpecial <============== ERROR
End With
End Sub
=====================================

I am using the above code to copy and paste values froM another
worksheet.
I WANT THE FORMAT AND VALUES COPIED, which the abovew does, however,
I get a "Method of 'PasteSpecial' of Object 'Range' Failed error".
If i click end ALL is fine, how can i rid the code of the error alert?


Corey



PCLIVE
Error in PasteSpecial ?
 
Maybe try:

Range("A1").activate
activesheet.PasteSpecial <============== ERROR

or

Range("A1").select
selection.pastespecial

Regards,
Paul
"Corey" wrote in message
...
=====================================
Sub TimeSheets()
Sheets("AAA").Range("A1:U41").Insert
With ActiveWorkbook
Sheets("ZZZ").Select
End With
With activeworksheet
Range("a1:u41").Copy
Sheets("AAA").Select
'Worksheets("AAA").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Range("A1").PasteSpecial <============== ERROR
End With
End Sub
=====================================

I am using the above code to copy and paste values froM another worksheet.
I WANT THE FORMAT AND VALUES COPIED, which the abovew does, however,
I get a "Method of 'PasteSpecial' of Object 'Range' Failed error".
If i click end ALL is fine, how can i rid the code of the error alert?


Corey

Corey
Error in PasteSpecial ?
 
Still get the same error also?

Ken Puls
Error in PasteSpecial ?
 
Sorry, I just looked at the line you said you errored on. Your code had
an issue higher up that should have errored out. There is no such
object as "activeworksheet". You'd need to change that to ActiveSheet.

Regardless, it isn't necessary to select each item all the time. Try
this shortened version of your code:

Sub TimeSheets()
Sheets("AAA").Range("A1:U41").Insert
Sheets("ZZZ").Range("a1:u41").Copy
Sheets("AAA").Range("A1").PasteSpecial Paste:=xlPasteAll
End Sub

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

Corey wrote:
Thanks for the reply.
I am trying the :

Range("A1").PasteSpecial Paste:=xlPasteAll

code but i still seems to get the same error.???

Corey
Error in PasteSpecial ?
 
Thnaks again.
I now get a different error when i run the simplified code you posted.

I get a :-




Automation error.
The object invoked has disconnected from its clients.





Never seen that before??


--
Regards

Corey
"Ken Puls" wrote in message
...
Sorry, I just looked at the line you said you errored on. Your code had
an issue higher up that should have errored out. There is no such object
as "activeworksheet". You'd need to change that to ActiveSheet.

Regardless, it isn't necessary to select each item all the time. Try this
shortened version of your code:

Sub TimeSheets()
Sheets("AAA").Range("A1:U41").Insert
Sheets("ZZZ").Range("a1:u41").Copy
Sheets("AAA").Range("A1").PasteSpecial Paste:=xlPasteAll
End Sub

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

Corey wrote:
Thanks for the reply.
I am trying the :

Range("A1").PasteSpecial Paste:=xlPasteAll

code but i still seems to get the same error.???

Ken Puls
Error in PasteSpecial ?
 
Very strange... There is nothing in that code that should cause an
automation error.

Have you tried saving your document, closing Excel and re-opening it?

Also, is there any other code that runs in this workbook?

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

Corey wrote:
Thnaks again.
I now get a different error when i run the simplified code you posted.

I get a :-




Automation error.
The object invoked has disconnected from its clients.





Never seen that before??

PCLIVE
Error in PasteSpecial ?
 
I actually don't get any errors running your code. Check to make sure your
sheet names match your code.

Regards,
Paul

"Corey" wrote in message
...
Still get the same error also?

Gary Keramidas
Error in PasteSpecial ?
 
will this do what you want?

Sub TimeSheets()
Sheets("AAA").Range("A1:U41").Insert
With ActiveWorkbook.Sheets("ZZZ")
.Select
.Range("a1:u41").Copy
Sheets("AAA").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Sheets("AAA").Range("A1:U42").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
End Sub

--


Gary


"Corey" wrote in message
...
=====================================
Sub TimeSheets()
Sheets("AAA").Range("A1:U41").Insert
With ActiveWorkbook
Sheets("ZZZ").Select
End With
With activeworksheet
Range("a1:u41").Copy
Sheets("AAA").Select
'Worksheets("AAA").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Range("A1").PasteSpecial <============== ERROR
End With
End Sub
=====================================

I am using the above code to copy and paste values froM another worksheet.
I WANT THE FORMAT AND VALUES COPIED, which the abovew does, however,
I get a "Method of 'PasteSpecial' of Object 'Range' Failed error".
If i click end ALL is fine, how can i rid the code of the error alert?


Corey

Jay
Error in PasteSpecial ?
 
Hi Corey-

The placement of the second 'end with' statement is producing your error;
within your 'with activeworksheet' block, you change the worksheet... Try
moving the second 'end with' statement up in the code as in the following:

Sub TimeSheets2()
Sheets("AAA").Range("A1:U41").Insert
With ActiveWorkbook
Sheets("ZZZ").Select
End With
With activeworksheet
Range("a1:u41").Copy
End With
Sheets("AAA").Select
'Worksheets("AAA").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Range("A1").PasteSpecial

End Sub


--
Thanks,
Jay


"PCLIVE" wrote:

Maybe try:

Range("A1").activate
activesheet.PasteSpecial <============== ERROR

or

Range("A1").select
selection.pastespecial

Regards,
Paul
"Corey" wrote in message
...
=====================================
Sub TimeSheets()
Sheets("AAA").Range("A1:U41").Insert
With ActiveWorkbook
Sheets("ZZZ").Select
End With
With activeworksheet
Range("a1:u41").Copy
Sheets("AAA").Select
'Worksheets("AAA").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Range("A1").PasteSpecial <============== ERROR
End With
End Sub
=====================================

I am using the above code to copy and paste values froM another worksheet.
I WANT THE FORMAT AND VALUES COPIED, which the abovew does, however,
I get a "Method of 'PasteSpecial' of Object 'Range' Failed error".
If i click end ALL is fine, how can i rid the code of the error alert?


Corey

Corey
Error in PasteSpecial ?
 
Yes cloed and re-run, with the same error.
I have other codes such as a code to convert cell ranges to times from a
(0730,1545) input value. Below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target,
Range("C7:C8,C11:C12,C15:C16,F7:F8,F11:F12,F15:F16 ,I7:I8,I11:I12,I15:I16,L7:L8,L11:L12,L15:L16,O7:O8 ,O11:O12,O15:O16,R7:R8,R11:R12,R15:R16,U7:U8,U11:U 12,U15:U16,V2:X2"))
Is Nothing Then
Exit Sub
' If Application.Intersect(Target,
Range("C7:C8,C11:C12,C15:C16,C19:C20,C23:C24,F7:F8 ,F11:F12,F15:F16,F19:F20,F23:C24,I7:I8,I11:I12,I15 :I16,I19:I20,I23:I24,L7:L8,L11:L12,L15:L16,L19:L20 ,L23:L24,O7:O8,O11:O12,O15:O16,O19:O20,O23:O24,R7: R8,R11:R12,R19:R20,R23:R24,U7:U8,U11:U12,U19:U20,U 23:U24,V2:X2"))
Is Nothing Then
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If ' this is code

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

AND

rivate Sub CommandButton1_Click()
'
' Graph1 Macro
' Macro recorded 4/10/2006 by Corey
'
Application.ScreenUpdating = False

'
Charts.Add
ActiveChart.ChartType = xl3DColumn
ActiveChart.SetSourceData Source:=Sheets("Utilization
Sheet").Range("K27")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "='Utilization
Sheet'!R3C23:R4C23"
ActiveChart.SeriesCollection(1).Name = "='Utilization Sheet'!R2C23"
ActiveChart.SeriesCollection(2).Values = "='Utilization Sheet'!R3C24"
ActiveChart.SeriesCollection(2).Name = "='Utilization Sheet'!R2C24"
ActiveChart.SeriesCollection(3).Values = "='Utilization Sheet'!R4C25"
ActiveChart.SeriesCollection(3).Name = "='Utilization Sheet'!R2C25"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Utilization
Sheet"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "John Coleiro"
.Axes(xlCategory).HasTitle = False
.Axes(xlSeries).HasTitle = False
.Axes(xlValue).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlSeries)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.WallsAndGridlines2D = False
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.SeriesCollection(1).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(2).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(3).Select
Selection.BarShape = xlCylinder

With ActiveChart
.Elevation = 15
.Perspective = 30
.Rotation = 40
.RightAngleAxes = False
.HeightPercent = 100
.AutoScaling = True
.ChartArea.Select
End With
Range("a1").Select
End Sub

x about 20 to create some charts.

Other than that NO other codes..

Corey....

Ken Puls
Error in PasteSpecial ?
 
Okay, just for fun, let's try stopping events while we do this...

Sub TimeSheets()
On Error Goto ExitPoint
Application.EnableEvents = False
Sheets("AAA").Range("A1:U41").Insert
Sheets("ZZZ").Range("a1:u41").Copy
Sheets("AAA").Range("A1").PasteSpecial Paste:=xlPasteAll
ExitPoint:
Application.EnableEvents = True
End Sub

Give that a shot and let me know...

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

Corey wrote:
Yes cloed and re-run, with the same error.
I have other codes such as a code to convert cell ranges to times from a
(0730,1545) input value. Below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target,
Range("C7:C8,C11:C12,C15:C16,F7:F8,F11:F12,F15:F16 ,I7:I8,I11:I12,I15:I16,L7:L8,L11:L12,L15:L16,O7:O8 ,O11:O12,O15:O16,R7:R8,R11:R12,R15:R16,U7:U8,U11:U 12,U15:U16,V2:X2"))
Is Nothing Then
Exit Sub
' If Application.Intersect(Target,
Range("C7:C8,C11:C12,C15:C16,C19:C20,C23:C24,F7:F8 ,F11:F12,F15:F16,F19:F20,F23:C24,I7:I8,I11:I12,I15 :I16,I19:I20,I23:I24,L7:L8,L11:L12,L15:L16,L19:L20 ,L23:L24,O7:O8,O11:O12,O15:O16,O19:O20,O23:O24,R7: R8,R11:R12,R19:R20,R23:R24,U7:U8,U11:U12,U19:U20,U 23:U24,V2:X2"))
Is Nothing Then
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If ' this is code

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

AND

rivate Sub CommandButton1_Click()
'
' Graph1 Macro
' Macro recorded 4/10/2006 by Corey
'
Application.ScreenUpdating = False

'
Charts.Add
ActiveChart.ChartType = xl3DColumn
ActiveChart.SetSourceData Source:=Sheets("Utilization
Sheet").Range("K27")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "='Utilization
Sheet'!R3C23:R4C23"
ActiveChart.SeriesCollection(1).Name = "='Utilization Sheet'!R2C23"
ActiveChart.SeriesCollection(2).Values = "='Utilization Sheet'!R3C24"
ActiveChart.SeriesCollection(2).Name = "='Utilization Sheet'!R2C24"
ActiveChart.SeriesCollection(3).Values = "='Utilization Sheet'!R4C25"
ActiveChart.SeriesCollection(3).Name = "='Utilization Sheet'!R2C25"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Utilization
Sheet"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "John Coleiro"
.Axes(xlCategory).HasTitle = False
.Axes(xlSeries).HasTitle = False
.Axes(xlValue).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlSeries)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.WallsAndGridlines2D = False
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.SeriesCollection(1).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(2).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(3).Select
Selection.BarShape = xlCylinder

With ActiveChart
.Elevation = 15
.Perspective = 30
.Rotation = 40
.RightAngleAxes = False
.HeightPercent = 100
.AutoScaling = True
.ChartArea.Select
End With
Range("a1").Select
End Sub

x about 20 to create some charts.

Other than that NO other codes..

Corey....

Corey
Error in PasteSpecial ?
 
I now get NO ERROR, but i also get NO Pasting into the other sheet??
Seems to Copy the range though???

"Ken Puls" wrote in message
...
Okay, just for fun, let's try stopping events while we do this...

Sub TimeSheets()
On Error Goto ExitPoint
Application.EnableEvents = False
Sheets("AAA").Range("A1:U41").Insert
Sheets("ZZZ").Range("a1:u41").Copy
Sheets("AAA").Range("A1").PasteSpecial Paste:=xlPasteAll
ExitPoint:
Application.EnableEvents = True
End Sub

Give that a shot and let me know...

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

Corey wrote:
Yes cloed and re-run, with the same error.
I have other codes such as a code to convert cell ranges to times from a
(0730,1545) input value. Below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target,
Range("C7:C8,C11:C12,C15:C16,F7:F8,F11:F12,F15:F16 ,I7:I8,I11:I12,I15:I16,L7:L8,L11:L12,L15:L16,O7:O8 ,O11:O12,O15:O16,R7:R8,R11:R12,R15:R16,U7:U8,U11:U 12,U15:U16,V2:X2"))
Is Nothing Then
Exit Sub
' If Application.Intersect(Target,
Range("C7:C8,C11:C12,C15:C16,C19:C20,C23:C24,F7:F8 ,F11:F12,F15:F16,F19:F20,F23:C24,I7:I8,I11:I12,I15 :I16,I19:I20,I23:I24,L7:L8,L11:L12,L15:L16,L19:L20 ,L23:L24,O7:O8,O11:O12,O15:O16,O19:O20,O23:O24,R7: R8,R11:R12,R19:R20,R23:R24,U7:U8,U11:U12,U19:U20,U 23:U24,V2:X2"))
Is Nothing Then
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If ' this is code

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

AND

rivate Sub CommandButton1_Click()
'
' Graph1 Macro
' Macro recorded 4/10/2006 by Corey
'
Application.ScreenUpdating = False

'
Charts.Add
ActiveChart.ChartType = xl3DColumn
ActiveChart.SetSourceData Source:=Sheets("Utilization
Sheet").Range("K27")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "='Utilization
Sheet'!R3C23:R4C23"
ActiveChart.SeriesCollection(1).Name = "='Utilization Sheet'!R2C23"
ActiveChart.SeriesCollection(2).Values = "='Utilization Sheet'!R3C24"
ActiveChart.SeriesCollection(2).Name = "='Utilization Sheet'!R2C24"
ActiveChart.SeriesCollection(3).Values = "='Utilization Sheet'!R4C25"
ActiveChart.SeriesCollection(3).Name = "='Utilization Sheet'!R2C25"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Utilization
Sheet"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "John Coleiro"
.Axes(xlCategory).HasTitle = False
.Axes(xlSeries).HasTitle = False
.Axes(xlValue).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlSeries)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.WallsAndGridlines2D = False
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.SeriesCollection(1).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(2).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(3).Select
Selection.BarShape = xlCylinder

With ActiveChart
.Elevation = 15
.Perspective = 30
.Rotation = 40
.RightAngleAxes = False
.HeightPercent = 100
.AutoScaling = True
.ChartArea.Select
End With
Range("a1").Select
End Sub

x about 20 to create some charts.

Other than that NO other codes..

Corey....

Corey
Error in PasteSpecial ?
 
Big fingers and little keys is my problem i think.

I had a typo.
I seems to do the trick WITHOUT ERROR now.

Thank You Ken, and all.

--
Regards

Corey


"Ken Puls" wrote in message
...
Okay, just for fun, let's try stopping events while we do this...

Sub TimeSheets()
On Error Goto ExitPoint
Application.EnableEvents = False
Sheets("AAA").Range("A1:U41").Insert
Sheets("ZZZ").Range("a1:u41").Copy
Sheets("AAA").Range("A1").PasteSpecial Paste:=xlPasteAll
ExitPoint:
Application.EnableEvents = True
End Sub

Give that a shot and let me know...

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

Corey wrote:
Yes cloed and re-run, with the same error.
I have other codes such as a code to convert cell ranges to times from a
(0730,1545) input value. Below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target,
Range("C7:C8,C11:C12,C15:C16,F7:F8,F11:F12,F15:F16 ,I7:I8,I11:I12,I15:I16,L7:L8,L11:L12,L15:L16,O7:O8 ,O11:O12,O15:O16,R7:R8,R11:R12,R15:R16,U7:U8,U11:U 12,U15:U16,V2:X2"))
Is Nothing Then
Exit Sub
' If Application.Intersect(Target,
Range("C7:C8,C11:C12,C15:C16,C19:C20,C23:C24,F7:F8 ,F11:F12,F15:F16,F19:F20,F23:C24,I7:I8,I11:I12,I15 :I16,I19:I20,I23:I24,L7:L8,L11:L12,L15:L16,L19:L20 ,L23:L24,O7:O8,O11:O12,O15:O16,O19:O20,O23:O24,R7: R8,R11:R12,R19:R20,R23:R24,U7:U8,U11:U12,U19:U20,U 23:U24,V2:X2"))
Is Nothing Then
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If ' this is code

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

AND

rivate Sub CommandButton1_Click()
'
' Graph1 Macro
' Macro recorded 4/10/2006 by Corey
'
Application.ScreenUpdating = False

'
Charts.Add
ActiveChart.ChartType = xl3DColumn
ActiveChart.SetSourceData Source:=Sheets("Utilization
Sheet").Range("K27")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "='Utilization
Sheet'!R3C23:R4C23"
ActiveChart.SeriesCollection(1).Name = "='Utilization Sheet'!R2C23"
ActiveChart.SeriesCollection(2).Values = "='Utilization Sheet'!R3C24"
ActiveChart.SeriesCollection(2).Name = "='Utilization Sheet'!R2C24"
ActiveChart.SeriesCollection(3).Values = "='Utilization Sheet'!R4C25"
ActiveChart.SeriesCollection(3).Name = "='Utilization Sheet'!R2C25"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Utilization
Sheet"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "John Coleiro"
.Axes(xlCategory).HasTitle = False
.Axes(xlSeries).HasTitle = False
.Axes(xlValue).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlSeries)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.WallsAndGridlines2D = False
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.SeriesCollection(1).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(2).Select
Selection.BarShape = xlCylinder
ActiveChart.SeriesCollection(3).Select
Selection.BarShape = xlCylinder

With ActiveChart
.Elevation = 15
.Perspective = 30
.Rotation = 40
.RightAngleAxes = False
.HeightPercent = 100
.AutoScaling = True
.ChartArea.Select
End With
Range("a1").Select
End Sub

x about 20 to create some charts.

Other than that NO other codes..

Corey....


All times are GMT +1. The time now is 04:50 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com