simplify vba code after macro record few minor task together :)

[DavidRoger]

Hi all,

Need to simplify a few minor task together.

First is copy paste value the selected cell. Then move 10 cells to the right and copy paste value. Last move 6 cells to the left.

Below is macro recorded:

Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(0, 10).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(0, -6).Range("A1").Select
End Sub

Thanks.

 

[Boswell]

Maybe this is what you are asking for?

Sub Macro1()
    
    ' copy paste selection as value
    Selection.Value = Selection.Value
    
    '  move 10 cells to the right and copy paste value
    Selection.Offset(0, 10).Value = Selection.Offset(0, 10).Value
    
    ' move 6 cells to the left
    Selection.Offset(0, 4).Select
    
End Sub

 

[Michael M]

If it works, why bother changing it ??
This may be an option, though

Sub Macro1()
With Selection
    .Value = .Value
    .Offset(0, 10).Value = .Offset(0, 10).Value
    .Offset(0, -6).Select
End With
End Sub

 

[DavidRoger]

Maybe this is what you are asking for?

Sub Macro1()
    
    ' copy paste selection as value
    Selection.Value = Selection.Value
    
    '  move 10 cells to the right and copy paste value
    Selection.Offset(0, 10).Value = Selection.Offset(0, 10).Value
    
    ' move 6 cells to the left
    Selection.Offset(0, 4).Select
    
End Sub

Hi Boswell,

That's what I am looking.
For the move 10 cells to the right and copy paste value it doesn't work.
There is formula in that cell.

Rephrase my task below:

Say selected cell is A3.

Task 1 - copy and paste value in cell A3; formula 1+1, after paste = 2
task 2 - move 10 cells to right, J3
task3 - copy and paste value in cell J3; formula 1+2, after paste = 3
task 4- move 6 cells to left, D3

 

[DavidRoger]

Hi Micheal and Boswell,

Work it out and the final look like this:

Sub Macro1()
    
    With Selection
    .Value = .Value
    .Offset(0, 9).Value = .Offset(0, 9).Value
    .Offset(0, 3).Select
    End With
    
End Sub[code]

Thank you Boswell for working code.
Thank you Micheal for better coding.

 

[Michael M]

Glad you got it sorted...

 

[DavidRoger]

Hi,

Need to reopen the question.

What if I need to copy paste value for a row of cells instead of a cells?

Says 17 cells to right.

Need to replace this part.

.Offset(0, 9).Value = .Offset(0, 9).Value