Copy and past as values

[Guest]

Hi
I am new to vba and macros. need urgent help
In Sheet 1, I am trying to copy a cell C1 and paste as values in cell D1 and
then from C2 to D2. The function needs to progress in this fashion one by
one. I want to create a macro and still be able to run it from a different
worksheet say Sheet 2.

Kindly help

Regards
Piyush

 

[Guest]

Try

Sub copy()
Dim myRange As Range
Set myRange = Sheets("sheet1").Range("C1:C20") ' Change to suit
For Each c In myRange
c.Offset(0, 1).Value = c.Value
Next
End Sub


Alt +F11, Insert module and paste code in.

Mike

 

[Guest]

You can copy the code below and paste it into the standard code
module by pressing Alt + F11. If the code module is dark, then
on the menu Insert>Module and then paste the code in.
You can assign a keyboard shortcut to run the code or run it by
clicking Tools>Macro>Macros and then selecting this macro before
clicking Run:

Sub cpy()
lr = Worksheets(1).Cells(Rows.Count, 3).End(xlUp).Row
' Gets the last used row number. If your data is in
' a different sheet change the number (1) to the
' appropriate index number.
For Each c In Worksheets(1).Range("$C$2:$C" & lr)
c.Copy
c.Offset(0, 1).PasteSpecial Paste:=xlValues
Next
Application.CutCopyMode = False
End Sub