Need to create a button that can copy cells, create a new sheet and then paste there

So I've got Sheet 1 with say

____A___B___C
1___m___i___c
2___r___o___s
3___o___f___t

I would like to create a button that can create a new sheet and paste A1 to C3 at the same location on the new sheet

and I need this to create a new sheet and do that everytime the button is pressed

can someone help me with this?




I've tried creating a shape and assigning a recorded macro to it where i did this:
1. create a new sheet (this part worked)
2. copy data (this worked)
3. pasted onto new sheet (did NOT work, instead it kept pasting onto the same sheet and 2 lines below the older version)

Maybe

 Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)

Change Sheet1 to the sheet to copy

you can add a button from the forms toolbar and right click > assign macro

http://www.bettersolutions.com/excel...B215612321.htm

Edit

or

Dim NewSht As Worksheet
Set NewSht = Sheets.Add
    NewSht.Move After:=Sheets(Sheets.Count)

    With Sheets("Sheet1")
        .Range("A1").CurrentRegion.Copy Destination:=NewSht.Range("A1")
    End With

VBA Noob

ive got

Sub Rectangle1_Click()
'
' Rectangle1_Click Macro
'

'
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Risk Assessment Outline").Select
    Range("B3:K7").Select
    Selection.Copy
    Sheets("Sheet4").Select
    Range("C3").Select
    Sheets("Sheet4").Select
    Range("B3").Select
    ActiveSheet.Paste
    Range("F10").Select
End Sub

also, another question is... can i create a button that copies data and pastes it directly under

say I copy A1 to B10

can I create a macro that will copy that and paste it at like... D1 to E10 the next time
and then the next time I click, it will create it at G1 to H10 (keeps going but leaves a space in between them)

say I copy A1 to B10

can I create a macro that will copy that and paste it at like... D1 to E10 the next time
and then the next time I click, it will create it at G1 to H10 (keeps going but leaves a space in between them)

Don't follow. You are now saying copy from Sheet A to Static Sheet B?

Try

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows("1:2").Copy Destination:=Rows(LastRow + 1).Resize(2)

or with the extra row

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows("1:2").Copy Destination:=Rows(LastRow + 2).Resize(2)

Try attached example

Need to change the resize as well

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows("1:100").Copy Destination:=Rows(LastRow + 2).Resize(100)