Macro copy and paste not pasting exactly whats in cell

Hi, the code below looks at column A for the active sheet and then copy and pastes rows based on column A to other sheets.

Please Login or Register  to view this content.

problem: when I run it,I am getting a bunch of "#ref"'s because the code is not copy and pasting exactly what is on the master sheet.. I cannot post the excel sheet because it has sensitive information on it but here is an example.
On the master sheet, in cell AB8 is: "=1694200*ReferenceSheet!J4 ". When i run the code it will copy it to the correct sheet in cell AB5 but with "=1694200*ReferenceSheet!J1 " , therefor the result: #ref . My code is not changing anything on the 'reference sheet' and I can manually change "J1" to "j4" and it works fine, but there is too much data to change manually. How can I make the data copy and paste correctly?


On another issue I have the following:

Please Login or Register  to view this content.

BUT, there are 15 sheets, and I need to do it for around 20 columns. What I am doing now is copy and pasting exactly what I posted above and changing the sheet names to the 15 sheets and then changing the columns one at a time. The result is 300 "with sheets(sheet)...(code).....End With. The procedure is too long to run. Is there a way to condense this? There are 15 sheets and the columns that i need to refer to are Z-AN.

Thanks in advance!! :D

Hi, hey1234,

please have a look at Forum Rule #3 and wrap your procedures with code-tags in the opening post. And please consider another thread title as well.

Ciao,
Holger

Hi, hey1234,

you either shoudl copy to the very same row, use absolut addresses for the cells or make use of Indirect - depending on the number of cells in question.

Please Login or Register  to view this content.

Ciao,
Holger

Sadly whenever you Copy/Paste or Copy/PasteSpecial formulas (even within the same worksheet), the cell references will adjust. This is true whether you use VBA or do it manually. To avoid this use:

Please Login or Register  to view this content.

in place of

Please Login or Register  to view this content.