Results 1 to 6 of 6

ActiveSheet.Paste macro error

This is a discussion on ActiveSheet.Paste macro error within the Excel Questions forums, part of the Question Forums category; In order to ultimately create a .prn file, I have to format my data in excel as custom, then copy ...

  1. #1
    New Member
    Join Date
    Jul 2010
    Posts
    3

    Default ActiveSheet.Paste macro error

    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

  2. #2
    Board Regular pedie's Avatar
    Join Date
    Apr 2010
    Location
    Unknown
    Posts
    3,817

    Default Re: ActiveSheet.Paste macro error

    Quote Originally Posted by jschipporeit View Post
    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

  3. #3
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,514

    Default Re: ActiveSheet.Paste macro error

    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
    HTH, Peter
    Please test any code on a copy of your workbook.

  4. #4
    New Member
    Join Date
    Jul 2010
    Posts
    3

    Default Re: ActiveSheet.Paste macro error

    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

  5. #5
    New Member
    Join Date
    Jun 2010
    Posts
    37

    Default Re: ActiveSheet.Paste macro error

    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.

  6. #6
    New Member
    Join Date
    Jul 2010
    Posts
    3

    Default Re: ActiveSheet.Paste macro error

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com