Paste Special???

[DejaVu]

I realize this may not be a programming question, but...
I've got a workbook with several sheets. On sheet 2, I have several
columns with the following formulas:
=Sheet1!C$674
=Sheet1!C$684
=Sheet1!C$694 ... etc

I'm wanting to copy these to another location. They are in Column D
(sheet2) currently, and I'm wanting to copy them to another location on
sheet2; Column C.

However whenever I copy them, the formulas change to:
=Sheet1!B$674
=Sheet1!B$684
=Sheet1!B$694 ... etc

Is there anyway to get them to stay the same?!?

Thanks in advance...

DejaVu

 

[tkstock]

Put a "$" in front of the column as well, this makes the formula
absolute from a column and row perspective

 

[DejaVu]

I understand that I can put a $ in front to make it absolute, but I
don't really want to have to go back in and type $ on every cell...

Any other alternatives??? Some sort of paste special?

 

[tkstock]

Just make a quick macro to do it for you:


Code
-------------------
set rng = selection
for each x in rng.cells
x.formula = left$(x.formula,9) & "$" & right(x.formula,len(x.formula)-9)
nex
-------------------


This code will add a $ for you (assuming the formulas start wit
"=Sheet1!")

HT

 

[DejaVu]

Thanks tkstock, just a little shove in the right direction....


Thanks again for your help!!

DejaVu

 

[tkstock]

Actually, I just realized I miscounted - everywhere you see a 9, replac
with an 8.

Sorry about that..

 

[DejaVu]

Thats ok, the sheet was not named 'Sheet1', so I had to modify that
anyway.

Thanks again tkstock



DejaVu

 

[STEVE BELL]

Build this into code: sets the formula to column C

MsgBox Left(Selection.FormulaR1C1, (Len(Selection.FormulaR1C1) - 4)) & 3


Dim cel as Range

Dim cel As Range

For Each cel In Selection
If Len(cel.FormulaR1C1) > 4 Then
cel.FormulaR1C1 = Left(cel.FormulaR1C1, (Len(cel.FormulaR1C1) - 4))
& 3
End If
Next

 

[Guest]

I have a similar question and have already posted it.

have a spreadsheet that uses ODBC/MS Query to connect to a database and
pull in part numbers each time the worksheet is opened the file refreshes.
The part numbers are put in a column, and the contents from this column are
copied to another worksheet using the copy paste special option.

Each time the ODBC connects and refreshes the data the cell numbers change
and causes the links from source to target not to work. For example if I have
20 part numbers in cells C1:C20 in the source worksheet, which are copied to
the target worksheet when I created the orginial links, the next time the
file is opened/refreshed the cells in the source worksheet may no be C50:C70
or some other number the target worksheet is still expecting to copy C1:C20.
The part numbers in the target still show the orginial values when I created
the orginial links from cells C1:C20 in the source worksheet.

How can I fix this so the copy-paste-special links will always copy the
current data contained in the source worksheet regardless of the cell numbers?