ActiveSheet.Paste macro error

jschipporeit

New Member
Joined
Jul 22, 2010
Messages
3
In order to ultimately create a .prn file, I have to format my data in excel as custom, then copy & paste into notepad, then select all, copy & paste back into excel. The whole point of this is to take data formatted as custom (a normal '987654' looks like '00000987654') and get it into excel formatted as text but to keep all the zeros as placeholders.

Everytime my macro reachs ActiveSheet.Paste I get a "Run-time error '1004' Paste method of Worksheet class failed" error. These are very simple but necessary steps. Any guidance with this is GREATLY appreciated! My code is below:

******

Range("A1:A28").Select
Selection.Copy
Application.CutCopyMode = False
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste <---error occurs here!
Rows("29:50").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-42
Range("C1").Select
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
In order to ultimately create a .prn file, I have to format my data in excel as custom, then copy & paste into notepad, then select all, copy & paste back into excel. The whole point of this is to take data formatted as custom (a normal '987654' looks like '00000987654') and get it into excel formatted as text but to keep all the zeros as placeholders.

Everytime my macro reachs ActiveSheet.Paste I get a "Run-time error '1004' Paste method of Worksheet class failed" error. These are very simple but necessary steps. Any guidance with this is GREATLY appreciated! My code is below:

******

Range("A1:A28").Select
Selection.Copy
Application.CutCopyMode = False
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste <---error occurs here!
Rows("29:50").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-42
Range("C1").Select


try this:

Range("A1:A28").Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Rows("29:50").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-42
Range("C1").Select
Sub End
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
The error occurs because there is nothing to paste as as result of

Code:
Application.CutCopyMode = False

Try

Code:
Range("A1:A28").Copy Destination:=Sheets("Sheet3").Range("A1")
Rows("29:50").ClearContents
 

jschipporeit

New Member
Joined
Jul 22, 2010
Messages
3
VoG,

Thank you for your reply. I thought that your solution had fixed my problem, but it turns out that it did not. Your fix does not have my data going through Notepad and therefore not coming back in as text. It fixes my error, but the data is not being formatted correctly.

I have to copy a column in excel (formatted as custom), open & paste into notepad, select all, copy and paste back into an excel template formatted as text. The purpose of these steps is to get '000123' (custom) to be '000123' as text.

Do you have any other ways that might help me out?!

Thanks for your help!

~Jen
 

philnotfil

New Member
Joined
Jun 23, 2010
Messages
37
Can't you just use the TEXT function for this?

=TEXT(cell reference, custom format) should return your custom number entries as text in the same format.
 

jschipporeit

New Member
Joined
Jul 22, 2010
Messages
3
Phil,

I am ultimately taking these '000123' numbers and saving them into a .PRN file so I don't believe I can do that with formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,254
Messages
5,657,622
Members
418,404
Latest member
Sneijders

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