indirect value to abs values

[- Bobb -]

I'll try to explain :
One xls file has a few tabs for my different investment accounts
(stock/funds/CD's etc)
On the Total Portfolio page there are references to the other tabs so as
I complete the other tabs, the portfolio tab gets updated too
Col A = name of investment
Col B = today's total value and for each week the current data gets
shifted to the right (col c,d,e,f, etc)to give a history.

tab 1 'Portfolio' Value

A B C
1 Stock name ='Stocks'!B2 previous weeks
2 another =Funds!B9 data is in these
3 bank account =CDs!B8 columns as time
4 another stock ='Stocks'!B4 goes by

Total =SUM

Tab 2 'Stocks '

A B C

1 stock 1000
2 name 1300
3 numbers 2700
4 equal 2000
5 the value 1850

Next week I have a macro to move "this weeks data" to the right (from B
to col C on both tabs ) and the I update current week on tab 2,3,4 etc -
overwriting column A again.

So my question is : as I shift the data to the right on tab 1 (from B
to C), I'd like the value in: tab 1 , B1-B4 to become the ABS values.
So when I shift the data to col c, I no longer see the formula C4
(='Stocks'!B4), but rather the value 2000. (since the target is what
I'm updating) same for others .

 

[Roger Govier]

Hi

Rather than taking the ABS() of values, I think you mean you want to
convert formulae into values.

In your macro, once the new column B has been inserted, have a line
Range("C1:C4").Value = Range("C1:C4).Value

 

[- Bobb -]

Hi

Rather than taking the ABS() of values, I think you mean you want to
convert formulae into values.

Exactly ( thanks for that - I'm only a casual user)

In your macro, once the new column B has been inserted, have a line
Range("C1:C4").Value = Range("C1:C4).Value

and how do I do that ? Right now I just copy Col B, I insert to col C
and choose to "shift current data to the right." Before I enter new
data, I then do what exactly ? I have to select col C and ...then type
that command ??? Then save that new macro ?

 

[Don Guillett]

Sub insertvalues()
Columns(2).Copy
Columns(3).Insert
Columns(3).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

 

[- Bobb -]

Got it - thanks !
( Once I select Edit macro , THEN I saw where to type the text - ran
fine)
I had never edited a macro before.

 

[Roger Govier]

Hi

Sorry not to have got back to you, but glad you worked it out anyway.
Best wishes for Christmas and the New Year