Error in PasteSpecial ?

[Corey]

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

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]

Thanks for the reply.
I am trying the :

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

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

 

[PCLIVE]

Maybe try:

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

or

Range("A1").select
selection.pastespecial

Regards,
Paul

 

[Corey]

Still get the same error also?

 

[Ken Puls]

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]

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

 

[Ken Puls]

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

 

[PCLIVE]

I actually don't get any errors running your code. Check to make sure your
sheet names match your code.

Regards,
Paul

 

[Gary Keramidas]

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

 

[Jay]

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

 

[Corey]

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:U12,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,U23: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 Where:=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]

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]

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

 

[Corey]

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.