+ Reply to Thread
Results 1 to 14 of 14

Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

  1. #1
    Registered User
    Join Date
    04-02-2013
    Location
    Portland, Oregon USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    Hello Everyone,

    I am trying to copy a named range "GradeReportHeadings" located on another sheet to the active sheet and transpose the data. I am using the following syntax but am getting an error:

    GradeReportHeadings.Copy
    ActiveSheet.Range("B5").PasteSpecial (Paste = xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False

    ---------------------------------------------------------------
    The a regular copy and paste works so I know that I can narrow down my error to the code above. Any ideas would be much appreciated. Thanks--Scott

  2. #2
    Registered User
    Join Date
    04-02-2013
    Location
    Portland, Oregon USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    The Code does not include the extra parenthesis to the right of the "pastespecial" command but am still getting an error--Thanks

    GradeReportHeadings.Copy
    ActiveSheet.Range("B5").PasteSpecial Paste = xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    How about

    Please Login or Register  to view this content.
    And

    Please Login or Register  to view this content.
    Does that work?

    Alf

  4. #4
    Registered User
    Join Date
    04-02-2013
    Location
    Portland, Oregon USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    Hello Alf,

    I got the error message "Run-time error 1004 Method Range of object_global failed for the Copy operation

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    Is the workbook / worksheet protected?

    Alf

  6. #6
    Registered User
    Join Date
    04-02-2013
    Location
    Portland, Oregon USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    Hello Alf, certain cells in the "Grades" worksheet are protected but I have worksheet protection turned off. I plan to turn it back on after I debug my application.

    On another note, when I type the syntax Range("GradeReportHeadings").Copy
    The intellisense feature pops up a choice of "copy" when I type the period after the closing parenthesis at the end so maybe you are on to something here?

  7. #7
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    How about recording a macro doing this "manually" i.e. selecting range "GradeReportHeadings" by using the "name box" (upper left), then copy and paste special to range B5 on active sheet and then examine the finished maro for "errors"

    Could it be there are somthing wrong with the name i.e. a traling space in the name or a different letter somwhere because you copy and paste commands looks ok?

    Alf

  8. #8
    Registered User
    Join Date
    04-02-2013
    Location
    Portland, Oregon USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    GradeBook Application Attached for Debugging Copy Range Error

    Hello Alf,

    I tried your suggestion and I was able to copy the range "GradebookReportHeadings" manually so thought I would try a different approach.

    I attached the code to a skeleton workbook with some student names in column B and the headings where they need to be starting in cell "N2" on a worksheet named "Grades" Hopefully this will assist us in getting to the bottom of this error.

    To use this application make sure to select at least one of the student names in column B. To delete the generated worksheets, just click the "Delete Gradebooks" button
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    Try this one

    Please Login or Register  to view this content.

  10. #10
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    Hi ScottyBee

    I've tested AB33's solution and it works like charm! I think your only problem was that in "PasteSpecial" command there you forgot to add the ":" i.e. instead of writing "Paste:= xlPasteAll"
    you wrote "Paste = xlPasteAll" and then I added to the confusion by my other advise but thanks to AB33 you should be ok now so don't forget to click on his star and give a rating to his help and don't forget to mark your thread solved.

    Alf

  11. #11
    Registered User
    Join Date
    04-02-2013
    Location
    Portland, Oregon USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    Hello Alf and AB33, your solutions worked great. I guess what hung me up was that after commenting out the paste command and still getting an error when executing the "GradeReportHeadings.Copy" by itself, I thought the problem lied in the sytax of the copy command and not the Paste Special part. Being new to programming, I guess this is a case where the Copy command has to have a counterpart paste or pastespecial command otherwise you get the the error.

    I marked the post as solved but could not find where to find Alf's star to give him credit where credit is due?

    Thanks again guys!!
    ScottyBee
    Last edited by ScottyBee; 04-07-2013 at 06:40 PM.

  12. #12
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    find where to find Alf's star to give him credit where credit is due
    In my oppinion AB33 is the one where "credit is due" but if you wish to spread "largesse to the multitude" I would also be happy to receive some.

    So click on the small star (bottom left) in one of my posts and add a comment if you like.

    Alf

  13. #13
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy Named Range on Different Sheet and Perform Paste Special on ActiveSheet

    Alf and Scottybee,
    Thanks for kind words!
    IMO,everyone who participate on the thread should be thanked. We should value people's time and effort. You are as equal deserved to be credited.

  14. #14
    Registered User
    Join Date
    04-02-2013
    Location
    Portland, Oregon USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Don't see Star to Give both of you Credit in Post

    Never mind, I found it. I am clicking on the Stars now

    Thanks---Scott
    Attached Files Attached Files
    Last edited by ScottyBee; 04-08-2013 at 06:35 PM. Reason: Just found the stars

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1