Copy and paste

[abac1]

Hi
I’ve a worksheet called abc 1, abc 2 abc 3 and so on until abc 30. I’d like to have a command button in each sheet and when clicked then excel automatically copy row d5:d10 into a5:a10 and copy e5:e10 into c5:c10 into the new sheet from the previous sheet sequentially.

Pls can someone help me.

thanks

 

[davesexcel]

Try it out.

Sub SendToNewSheet()
    Dim sh As Worksheet, ws As Worksheet
    Dim Rng1 As Range, Rng2 As Range
    Dim nRng1 As Range, nRng2 As Range

    Set sh = ActiveSheet
    With sh
        Set Rng1 = .Range("D5:D10")
        Set Rng2 = .Range("E5:E10")
    End With

    Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
    'ws.Name="SheetName" 'name sheet if required.
    With ws
        Set nRng1 = .Range("A5:A10")
        Set nRng2 = .Range("C5:C10")
    End With

    nRng1.Value = Rng1.Value
    nRng2.Value = Rng2.Value


End Sub

 

[abac1]

Thank you