Make Values from formulas

[GregR]

I have a spreadheet that has a column, titled "Running Totals". This
column moves each month after the first of the month. When I move that
column, I want to automatically convert any formulas to the left of the
column into their numeric value. How? TIA

Greg

[Ardus Petus]

Select all columns to be updated
Edit>Copy
Edit>Paste special check Values

HTH
--
AP

"GregR" <[email protected]> a écrit dans le message de news:
[email protected]...
>I have a spreadheet that has a column, titled "Running Totals". This
> column moves each month after the first of the month. When I move that
> column, I want to automatically convert any formulas to the left of the
> column into their numeric value. How? TIA
>
> Greg
>

[Don Guillett]

this may do it all for you since it copies the last column to the next
column and then converts the formulas in the previous last column to values

Sub valuelastcoltonest()
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Columns(lc).Copy Columns(lc + 1)
Columns(lc).Value = Columns(lc).Value
End Sub


--
Don Guillett
SalesAid Software
[email protected]
"GregR" <[email protected]> wrote in message
news:[email protected]...
>I have a spreadheet that has a column, titled "Running Totals". This
> column moves each month after the first of the month. When I move that
> column, I want to automatically convert any formulas to the left of the
> column into their numeric value. How? TIA
>
> Greg
>

[MartinShort]

Use "Paste Special" and select "Values". To convert this to VBA, record it in the Macro Recorder and copy and paste the code produced.

[Sean]

try something like this

Sub example()

Dim first As Integer

Worksheets("Sheet3").Activate

Range("A1").Select

first = 1

Do While ActiveCell.Offset(0, first).Value <> "Running Totals"
first = first + 1
Loop

Columns(first).Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Range("A1").Select

End Sub

you would have to run this every month. this assumes that the sheet is
called "sheet3" so you would have to change that appropriately. It also
assumes the heading is in row 1 so if it is not, you have to change the 0 in
Do While ActiveCell.Offset(0, first).Value <> "Running Totals" to 1 or 2 or
whatever depending on which row in is in.

hope that helps


"GregR" wrote:

> I have a spreadheet that has a column, titled "Running Totals". This
> column moves each month after the first of the month. When I move that
> column, I want to automatically convert any formulas to the left of the
> column into their numeric value. How? TIA
>
> Greg
>
>

[GregR]

Don, the running total column is not the last column. I need something
to find the running total column, count the columns to the left of it,
however omit column "A", and convert any formlas into values. TIA

Greg

Don Guillett wrote:
> this may do it all for you since it copies the last column to the next
> column and then converts the formulas in the previous last column to values
>
> Sub valuelastcoltonest()
> lc = Cells(1, Columns.Count).End(xlToLeft).Column
> Columns(lc).Copy Columns(lc + 1)
> Columns(lc).Value = Columns(lc).Value
> End Sub
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "GregR" <[email protected]> wrote in message
> news:[email protected]...
> >I have a spreadheet that has a column, titled "Running Totals". This
> > column moves each month after the first of the month. When I move that
> > column, I want to automatically convert any formulas to the left of the
> > column into their numeric value. How? TIA
> >
> > Greg
> >

[GregR]

Sean, if I wanted to skip Column "A", would I select B1. Your other
assumptions are correct, except it is always the first sheet in the
workbook.

Greg
Sean wrote:
> try something like this
>
> Sub example()
>
> Dim first As Integer
>
> Worksheets("Sheet3").Activate
>
> Range("A1").Select
>
> first = 1
>
> Do While ActiveCell.Offset(0, first).Value <> "Running Totals"
> first = first + 1
> Loop
>
> Columns(first).Select
>
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
>
> Application.CutCopyMode = False
>
> Range("A1").Select
>
> End Sub
>
> you would have to run this every month. this assumes that the sheet is
> called "sheet3" so you would have to change that appropriately. It also
> assumes the heading is in row 1 so if it is not, you have to change the 0 in
> Do While ActiveCell.Offset(0, first).Value <> "Running Totals" to 1 or 2 or
> whatever depending on which row in is in.
>
> hope that helps
>
>
> "GregR" wrote:
>
> > I have a spreadheet that has a column, titled "Running Totals". This
> > column moves each month after the first of the month. When I move that
> > column, I want to automatically convert any formulas to the left of the
> > column into their numeric value. How? TIA
> >
> > Greg
> >
> >

[Sean]

Yes and also change Columns(first).Select to Columns(first + 1).Select.

note also that is only changes the immediate left column. I read in one of
your replies that you wanted to count columns to the left. Does that mean you
want to change all columns to the left except column A?

"GregR" wrote:

> Sean, if I wanted to skip Column "A", would I select B1. Your other
> assumptions are correct, except it is always the first sheet in the
> workbook.
>
> Greg
> Sean wrote:
> > try something like this
> >
> > Sub example()
> >
> > Dim first As Integer
> >
> > Worksheets("Sheet3").Activate
> >
> > Range("A1").Select
> >
> > first = 1
> >
> > Do While ActiveCell.Offset(0, first).Value <> "Running Totals"
> > first = first + 1
> > Loop
> >
> > Columns(first).Select
> >
> > Selection.Copy
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> >
> > Application.CutCopyMode = False
> >
> > Range("A1").Select
> >
> > End Sub
> >
> > you would have to run this every month. this assumes that the sheet is
> > called "sheet3" so you would have to change that appropriately. It also
> > assumes the heading is in row 1 so if it is not, you have to change the 0 in
> > Do While ActiveCell.Offset(0, first).Value <> "Running Totals" to 1 or 2 or
> > whatever depending on which row in is in.
> >
> > hope that helps
> >
> >
> > "GregR" wrote:
> >
> > > I have a spreadheet that has a column, titled "Running Totals". This
> > > column moves each month after the first of the month. When I move that
> > > column, I want to automatically convert any formulas to the left of the
> > > column into their numeric value. How? TIA
> > >
> > > Greg
> > >
> > >
>
>

[Don Guillett]

try
Sub findrunningtotalcol()
mc = Rows(1).Find("Running Total").Column - 1
MsgBox mc
Range(Cells(1, 2), Cells(1, mc)).EntireColumn.Value = _
Range(Cells(1, 2), Cells(1, mc)).EntireColumn.Value
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"GregR" <[email protected]> wrote in message
news:[email protected]...
> Don, the running total column is not the last column. I need something
> to find the running total column, count the columns to the left of it,
> however omit column "A", and convert any formlas into values. TIA
>
> Greg
>
> Don Guillett wrote:
>> this may do it all for you since it copies the last column to the next
>> column and then converts the formulas in the previous last column to
>> values
>>
>> Sub valuelastcoltonest()
>> lc = Cells(1, Columns.Count).End(xlToLeft).Column
>> Columns(lc).Copy Columns(lc + 1)
>> Columns(lc).Value = Columns(lc).Value
>> End Sub
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "GregR" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a spreadheet that has a column, titled "Running Totals". This
>> > column moves each month after the first of the month. When I move that
>> > column, I want to automatically convert any formulas to the left of the
>> > column into their numeric value. How? TIA
>> >
>> > Greg
>> >
>

[GregR]

Sean, yes that's right

Greg
Sean wrote:
> Yes and also change Columns(first).Select to Columns(first + 1).Select.
>
> note also that is only changes the immediate left column. I read in one of
> your replies that you wanted to count columns to the left. Does that mean you
> want to change all columns to the left except column A?
>
> "GregR" wrote:
>
> > Sean, if I wanted to skip Column "A", would I select B1. Your other
> > assumptions are correct, except it is always the first sheet in the
> > workbook.
> >
> > Greg
> > Sean wrote:
> > > try something like this
> > >
> > > Sub example()
> > >
> > > Dim first As Integer
> > >
> > > Worksheets("Sheet3").Activate
> > >
> > > Range("A1").Select
> > >
> > > first = 1
> > >
> > > Do While ActiveCell.Offset(0, first).Value <> "Running Totals"
> > > first = first + 1
> > > Loop
> > >
> > > Columns(first).Select
> > >
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > SkipBlanks _
> > > :=False, Transpose:=False
> > >
> > > Application.CutCopyMode = False
> > >
> > > Range("A1").Select
> > >
> > > End Sub
> > >
> > > you would have to run this every month. this assumes that the sheet is
> > > called "sheet3" so you would have to change that appropriately. It also
> > > assumes the heading is in row 1 so if it is not, you have to change the 0 in
> > > Do While ActiveCell.Offset(0, first).Value <> "Running Totals" to 1 or 2 or
> > > whatever depending on which row in is in.
> > >
> > > hope that helps
> > >
> > >
> > > "GregR" wrote:
> > >
> > > > I have a spreadheet that has a column, titled "Running Totals". This
> > > > column moves each month after the first of the month. When I move that
> > > > column, I want to automatically convert any formulas to the left of the
> > > > column into their numeric value. How? TIA
> > > >
> > > > Greg
> > > >
> > > >
> >
> >

[GregR]

Don, thank you very much

Greg
Don Guillett wrote:
> try
> Sub findrunningtotalcol()
> mc = Rows(1).Find("Running Total").Column - 1
> MsgBox mc
> Range(Cells(1, 2), Cells(1, mc)).EntireColumn.Value = _
> Range(Cells(1, 2), Cells(1, mc)).EntireColumn.Value
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "GregR" <[email protected]> wrote in message
> news:[email protected]...
> > Don, the running total column is not the last column. I need something
> > to find the running total column, count the columns to the left of it,
> > however omit column "A", and convert any formlas into values. TIA
> >
> > Greg
> >
> > Don Guillett wrote:
> >> this may do it all for you since it copies the last column to the next
> >> column and then converts the formulas in the previous last column to
> >> values
> >>
> >> Sub valuelastcoltonest()
> >> lc = Cells(1, Columns.Count).End(xlToLeft).Column
> >> Columns(lc).Copy Columns(lc + 1)
> >> Columns(lc).Value = Columns(lc).Value
> >> End Sub
> >>
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> [email protected]
> >> "GregR" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have a spreadheet that has a column, titled "Running Totals". This
> >> > column moves each month after the first of the month. When I move that
> >> > column, I want to automatically convert any formulas to the left of the
> >> > column into their numeric value. How? TIA
> >> >
> >> > Greg
> >> >
> >

[Don Guillett]

glad it helped

--
Don Guillett
SalesAid Software
[email protected]
"GregR" <[email protected]> wrote in message
news:[email protected]...
> Don, thank you very much
>
> Greg
> Don Guillett wrote:
>> try
>> Sub findrunningtotalcol()
>> mc = Rows(1).Find("Running Total").Column - 1
>> MsgBox mc
>> Range(Cells(1, 2), Cells(1, mc)).EntireColumn.Value = _
>> Range(Cells(1, 2), Cells(1, mc)).EntireColumn.Value
>> End Sub
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "GregR" <[email protected]> wrote in message
>> news:[email protected]...
>> > Don, the running total column is not the last column. I need something
>> > to find the running total column, count the columns to the left of it,
>> > however omit column "A", and convert any formlas into values. TIA
>> >
>> > Greg
>> >
>> > Don Guillett wrote:
>> >> this may do it all for you since it copies the last column to the next
>> >> column and then converts the formulas in the previous last column to
>> >> values
>> >>
>> >> Sub valuelastcoltonest()
>> >> lc = Cells(1, Columns.Count).End(xlToLeft).Column
>> >> Columns(lc).Copy Columns(lc + 1)
>> >> Columns(lc).Value = Columns(lc).Value
>> >> End Sub
>> >>
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> [email protected]
>> >> "GregR" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I have a spreadheet that has a column, titled "Running Totals". This
>> >> > column moves each month after the first of the month. When I move
>> >> > that
>> >> > column, I want to automatically convert any formulas to the left of
>> >> > the
>> >> > column into their numeric value. How? TIA
>> >> >
>> >> > Greg
>> >> >
>> >
>