Automatically shifting cells

Hello. first time posting on the forum. Seems to be great help over here so I decided to give it a shot.


I'm looking to create a program that enables the macro to run with placing the desired cell blocks (A3:F12) from Sheet1 into Sheet2, and when i press the shortcut key again, it automatically places the cell blocks underneath the previous data that was pasted in the Sheet2. The goal of this is to keep a history of the data into the Sheet2.

I'm new to this, especially with VBA but is there any possible way to create this type of macro program? Thank you in advance.

see if this gets you started

Sub MoveData()
Dim SrcRng As Range
Dim LastC As Range

Set LastC = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Set SrcRng = Sheets("Sheet1").Range("A3:F12")

    SrcRng.Copy LastC
    Application.CutCopyMode = False

End Sub

VBA Noob

Thanks for the help.


I plugged in the code and what it does is after pasting the first set of data, the cells don't shift down, but rather erases the first row from the very top and continues to do so everytime i press the shortcut key until the all rows of the data are disappeared. I think you could be onto something though.

Would you know what can be solved in this case?


Edit: I just realized, it only deletes the rows when i stay in the Sheet2 window. If im in the Sheet1 window and press the shortcut key, data is only replaced in Sheet2, not added below.

The code posts range A3:F12 in sheet 1 to the either the last cell in Col A in Sheet 2 or if it's blank then A2

can you post a sample of what you're after if the above is wrong

VBA Noob

Ok. I hope i can explain this clearly.

I would like to move cells A3:F12 from Sheet1 to cell blocks A1:F10 in Sheet2.

Now, when i want to change the data (from Sheet1) and press the shortcut key, it will transfer the new data below the first set of data (in Sheet2) to cells A13:F21 (since I would like to keep 2 rows of space).

I would like to have it continue to place sets of datas below eachother infinitely (keeping 2 row spaces free between each set).

Does this help a little?

* I attached the file, the one i'm working on.

Is this any better

Sub MoveData()
Dim SrcRng As Range
Dim LastC As Range


Set SrcRng = Sheets("Sheet1").Range("A3:F12")

If WorksheetFunction.CountA(Sheets("Sheet2").Columns(1)) > 1 Then
    Set LastC = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(3)
Else
    Set LastC = Sheets("Sheet2").Range("A1")
End If

    SrcRng.Copy LastC
    Application.CutCopyMode = False

Set LastC = Nothing
Set SrcRng = Nothing
End Sub

VBA Noob