Excel macro for converting numbers to values in thousands

[monish_rk]

Dear friends

I am looking for a macro which would convert a copied range of cells into thousands (in values) in another sheet. I frequently copy and paste values, and then perform a pastespecial function by dividing by 1000. I tried to record a macro to do this, but to no avail.

May I request your assistance in this.

The code that I recorded is as follows, but it does not run appropriately.

Thanks in advance

Regards
monish_rk

Sub Macro1()
'
' Macro1 Macro
'
'
'
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "=R[-3]C/1000"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[ketsat]

This macro will paste the values of what you have copied, and then divide each cell by 1000

Sub PasteSpecial1000s()
Dim Qcell As Range

'Paste the values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'Loop through each cell and ddivide it by 1000
For Each Qcell In Selection
Qcell.Value = Qcell.Value / 100
Next Qcell

End Sub

 

[monish_rk]

Dear ketsat

Thanks for your assitance. I tried the macro and noted that there was an error while running the same. VB indicated that the following portion of the code was not running properly.

'Paste the values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False

When I ran the other piece of the code, it ran very well. I am wondering if you can still help.

Thanks.

 

[ketsat]

Hmm, it works fine for me on Excel07.
I can try it on Excel03, when I get to work.

What error are you getting?

 

[ketsat]

It works fine on Excel 2003 too.

 

[PATSYS]

Dear ketsat

Thanks for your assitance. I tried the macro and noted that there was an error while running the same. VB indicated that the following portion of the code was not running properly.

'Paste the values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False

When I ran the other piece of the code, it ran very well. I am wondering if you can still help.

Thanks.

The code provided by ketsat requires that you already are on the copy mode and have selected/copied the destination range before you run it.

 

[dave3009]

From what I read

Sub Macro1()
'..Put 1000 in a cell and copy it
With Range("E1")
    .Value = 1000
    .Copy
End With
'..Now apply pastespecial with values and divide, you will need to adjust all ranges
Range("A1:D1000").PasteSpecial Paste:=xlPasteValues, Operation:=xlDivide
End Sub