VBA to utilize a range name listed in a table

woodsy74

New Member
Joined
Jun 29, 2012
Messages
18
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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
Back
Top