Moving a range one column to the right without losing the cell references

Hello,

I really tried to figure this one out on my own by looking at other posts and experimenting, but I'm stumped.

I have a named range (let's call it "PatRange") where 1) the first column of data refers to a different line (let's call it "Separate_Line") on the same page, with a cell that is added every year through a fill right piece of code, and 2) the other columns refer to the first column AND to an unnamed table on another sheet that never moves. I would like:

1) for "PatRange" to move over by one column every new year
2) for the first column to refer to the newly added cell on "Separate_Line"
and 3) for the other columns to refer to that static table.

Extra, perhaps superfluous information:
1) "PatRange" uses the table on the other sheet by multiplying the first column of "PatRange" by a cell in that table.
2) The code I use to fill right on "Separate_Line" is:

Please Login or Register  to view this content.

3) The values in the unnamed table will be updated annually with hard-coded values, even though they don't move

I've found code that moves the range, but does not move the actual values along with the range. I've also read in a few places that I shouldn't use "select," which that code did.

I've attached a sample of what I would like, including 2 iterations. I hope I have been clear; if you have any suggestions for how I can be a better question poster, please let me know!

Year 1
2013 2014 2015 2016 2017
20

"PatRange"
20 20 40 60 80
20 40 60 80 100
20 60 80 100 120
20 80 100 120 140
20 100 120 140 160
20 120 140 160 180

Year 2
2013 2014 2015 2016 2017 2018
20 25
"PatRange" moved one column to the right
25 25 50 75 100
25 50 75 100 125
25 75 100 125 150
25 100 125 150 175
25 125 150 175 200
25 150 175 200 225

Year 3
2013 2014 2015 2016 2017 2018 2019
20 25 29
"PatRange" moved yet another column to the right
29 29 58 87 116
29 58 87 116 145
29 87 116 145 174
29 116 145 174 203
29 145 174 203 232
29 174 203 232 261


This table is on a different sheet, and the cells don't move
1 2 3 4
2 3 4 5
3 4 5 6
4 5 6 7
5 6 7 8
6 7 8 9


Thank you in advance,
Pat

Hi Pat

I believe the reason you're no getting any traffic is because the Sample File you attached does not reflect your Actual File.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.

Hi Pat

You indicate

I have the macro figured out for the first 2 items on that list

I see the Code for the First Item on the List

The formulas in rows 2-9 fill right

I don't see the Code for the Second Item on the List...do you have it?

The numbers in the first 10 rows of Column AO fill to the right into Column AP

Hi Pat

I found it...sorry about that. The Code should be placed in a General Module but we'll address that later.

Hi Pat

Create a Named Range (I called it "OtherStuff") referring to all the Cells in the three tables in "Hist. & Proj. Prices - Chart".

Put this Code in a General Module

Please Login or Register  to view this content.

After you've run your 2 other procedures run this Code.

Let me know of issues...

Hi Pat

Can't get back to this until late Sunday...have Family Matters to attend to...

Hi Pat

I looked at this and I believe the only solution is

to go through all the formulas and adding absolute cell references

Hi Pat

Glad you got it sorted.

You're welcome...glad I could help. Thanks for the Rep.