Macro help - very simple.

[jondon]

I am trying to join two macros as follows:

Sub updatechange()
'
' updatechange Macro
'
' Keyboard Shortcut: Ctrl+u
'

Sheets("Summary").Select
Range("O5:O14").Select
Selection.Copy
Sheets("Changes to Plan").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Summary").Select
Range("O21:O30").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Changes to Plan").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

The problem is I need the second copy/paste to happen 4 columns over from the first one runs. The operator will be selecting the location for the start.

I have little/no knowledge of VB/syntax and am feeling my through.

Any help greatly appreciated.

 

[dave3009]

How is you decide where to paste? It currently will paste wherever the active cell is.

But something like

Sub updatechange()
' Keyboard Shortcut: Ctrl+u
With Application
    .ScreenUpdating = False
        Sheets("Summary").Range("O5:O14").Copy
        Sheets("Changes to Plan").Range("A1").PasteSpecial xlPasteValues
        Sheets("Summary").Range("O21:O30").Copy
        Sheets("Changes to Plan").Range("E1").PasteSpecial xlPasteValues
    .ScreenUpdating = True
End With
End Sub

Altering the bit in bold to suit

 

[jondon]

the user selects the paste location for each when they are run individually - but the second paste operation always takes place 4 columns to the right for the second one on the same row.

It is iterative. when a change to the worksheet in general is made a new "entry" is logged and the the initial paste point will be 11 rows below the last entry. I though that having the user make the first selection (they are used to it ) and I could bet the second bit to run based on "4 to the left logic". That I have no idea how to do.

Just saw your answer - how do I say user selection instead of Range("a1") and "4 to the left" instead of Range ("e1")?

 

[dave3009]

Assuming the user picks the cell then fires the macro then try

Sub updatechange()
' Keyboard Shortcut: Ctrl+u
Dim currCell As String
With Application
    .ScreenUpdating = False
        currCell = ActiveCell.Address
        Sheets("Summary").Range("O5:O14").Copy
        Sheets("Changes to Plan").Range(currCell).PasteSpecial xlPasteValues
        Sheets("Summary").Range("O21:O30").Copy
        Sheets("Changes to Plan").Range(currCell).Offset(0, 4).PasteSpecial xlPasteValues
    .ScreenUpdating = True
End With
End Sub

 

[jondon]

I believe the scientific term is "Bingo!"
Perfect - thanks for the quick assist!

 

[dave3009]

No problem, any time

Thanks for the feedback :D