asked on # Excel Copy Paste While Holding Cell References Intact

HELLO...

I have an immediate need, for a tip to use...when copy/pasting in one row to the next. I've just got to believe there's a way to do this.

In a given spreadsheet, I have the following formulas in COLUMN-D:

D1--> MIN_QTY (not a formula...only a Column Heading)

D2--> =Sheet2!D2

D3--> =Sheet2!F2

D4--> =Sheet2!H2

D5--> =Sheet2!J2

D6--> =Sheet2!L2

What *MUST* occur...is that in rows 7-thru-11, I need to end up with the following:

D7--> =Sheet2!D3

D8--> =Sheet2!F3

D9--> =Sheet2!H3

D10--> =Sheet2!J3

D11--> =Sheet2!L3

...and so forth...repeating this paste strategy...for the next 5,000 rows! I thought of using some sort of Transposing method, but I'm getting a bit lost with that. This is only an example...and will apply to COLUMNS A, C, and G as well, in the current Sheet.

The problem is, that I *cannot* alter the source data (i.e. "Sheet2"), as this comes directly from a database report extraction. I also thought of using some sort of clever use of the "$" character, to hold the relative positions in check, but that won't work...or, I'm just using it wrong. :-)

Please HELP! I'm stuck, and I must get this project done today for an Executive. I'd truly appreciate any input here...THANKS!...Mark

I have an immediate need, for a tip to use...when copy/pasting in one row to the next. I've just got to believe there's a way to do this.

In a given spreadsheet, I have the following formulas in COLUMN-D:

D1--> MIN_QTY (not a formula...only a Column Heading)

D2--> =Sheet2!D2

D3--> =Sheet2!F2

D4--> =Sheet2!H2

D5--> =Sheet2!J2

D6--> =Sheet2!L2

What *MUST* occur...is that in rows 7-thru-11, I need to end up with the following:

D7--> =Sheet2!D3

D8--> =Sheet2!F3

D9--> =Sheet2!H3

D10--> =Sheet2!J3

D11--> =Sheet2!L3

...and so forth...repeating this paste strategy...for the next 5,000 rows! I thought of using some sort of Transposing method, but I'm getting a bit lost with that. This is only an example...and will apply to COLUMNS A, C, and G as well, in the current Sheet.

The problem is, that I *cannot* alter the source data (i.e. "Sheet2"), as this comes directly from a database report extraction. I also thought of using some sort of clever use of the "$" character, to hold the relative positions in check, but that won't work...or, I'm just using it wrong. :-)

Please HELP! I'm stuck, and I must get this project done today for an Executive. I'd truly appreciate any input here...THANKS!...Mark

Microsoft ExcelVBA

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Thank you both *SOOOOOO* much for responding. I'm going to try both suggestions, and will award the points by day's end. It's *VERY* much appreciated!...Mark

I'm awarding you both the points...as both of your solutions were fast and fabulous! Thanks for chiming in guys...again, it's most appreciated!...Mark