Results 1 to 4 of 4

Thread: VBA to utilize a range name listed in a table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2012
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to utilize a range name listed in a table

    I have a table in my workbook with 3 columns - path, filename, and paste location. The code loops through my table opens up the file and path I have listed, copies some data, and then I am trying to paste that data into my workbook in the range name I have listed as paste location. After copying the data I go back to my workbook and I am trying to use Range(c.Offset(0,3).Value).Select to paste the data in that location but I keep getting a debug. I've double checked that the what I have listed in the past location column is indeed the range name I have in my workbook.

    Am I able to do what I am trying to do? Let me know if you have any questions. Thanks.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,490
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA to utilize a range name listed in a table

    Assuming c is a range, if you want to paste to c.Offset(0,3) have you tried:
    Code:
    c.Offset(0,3).Pastespecial paste:= xlPasteAll  'change this to xlValues if you want only the values pasted.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Jun 2012
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to utilize a range name listed in a table

    I had only tried to Select first and then I was going to Paste. I just tried going right to Paste but I get the same error Method Range of Object Global failed. When I debug and hover over the c.offset(0,3) I do see the range name I want to utilize.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,490
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA to utilize a range name listed in a table

    Quote Originally Posted by woodsy74 View Post
    I had only tried to Select first and then I was going to Paste. I just tried going right to Paste but I get the same error Method Range of Object Global failed. When I debug and hover over the c.offset(0,3) I do see the range name I want to utilize.
    Hard to help if you don't post the code that's causing problems.

    When you get the error, enter in the Immediate Window: ?c.address - what do you get? Enter ?c.offset(0,3).address - what do you get? Enter ?c.parent.name - what do you get. Are the results of these queries what you expected?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

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
  •