paste errors

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
503
I am trying to paste and running into some errors. If I use the code below, I get a "PasteSpecial method of range class failed."

Sheets("QC5003.8 PCB Stab Rec - Storage").Range("BE5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


If I use the code listed this way, I get a "Select method of range class failed."

Sheets("QC5003.8 PCB Stab Rec - Storage").Range("BE5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

How can I get my data pasted into this workbook?

Thanks,

Robert
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
The first code should work. For the second you would need

Code:
Sheets("QC5003.8 PCB Stab Rec - Storage").select
Range("BE5").Select
Selection.PasteSpecial Paste:=xlPasteValues

Are you sure the clipboard isn't empty.
 

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
503
Still no success. I get the following error with the code listed.

Run time error '1004':
PasteSpecial method of Range class failed


Sheets("QC5003.8 PCB Stab Rec - Storage").Select
Range("BE5").Select
Selection.PasteSpecial Paste:=xlPasteValues

Line in red is flagged by the debugger when I open the VBA editor. This code resides in a public module and is not found in a code window for a specific sheet. I have rewritten this a couple of times, and saved and restarted the worksheet, and still to no avail.

Thanks for any direction.

Robert
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Probably the clipboard is empty (nothing to paste). Can you post the code from the line where you Copy to the line that fails.
 

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
503

ADVERTISEMENT

Here is the code. I am in workbook B and copying data to Workbook A (File1).

Range("B1").End(xlDown).Offset(0, -1).Copy 'From Workbook B

ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks(File1).Activate 'Workbook A

Sheets("QC5003.8 PCB Stab Rec - Storage").Visible = True
Sheets("QC5003.8 PCB Stab Rec - Storage").Select
Range("BE5").Select
Selection.PasteSpecial Paste:=xlPasteValues

Does the Save and Close erase the clipboard? If so then I have to do a back and forth between the two workbooks correct?

Thanks,

Robert
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this

Code:
Range("B1").End(xlDown).Offset(0, -1).Copy 'From Workbook B
With Workbooks(File1)
    .Sheets("QC5003.8 PCB Stab Rec - Storage").Visible = True
    .Sheets("QC5003.8 PCB Stab Rec - Storage").Range("BE5").PasteSpecial Paste:=xlPasteValues
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
 

Watch MrExcel Video

Forum statistics

Threads
1,127,600
Messages
5,625,744
Members
416,132
Latest member
Chandan Choubey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top