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

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

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

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

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

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

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

Still get the same error also?

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?

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

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