How do I copy and paste data to another worksheets row while keeping and shifting old data downwards

[Mo796]

I am currently working on a financial spread sheet which will track certain data from one page and paste it to the first row of a table on another page. However, I do not want it to overwrite old data as I am trying to keep a timeline of this data as it changes, so I would like the old data that was entered to be moved downwards by one row.
My current macro successfully moves data from my "Balance Entry Sheet" to the "Balance Timelines" sheet Row A3 as intended, but it will overwrite any data already entered and I cant seem to find the right combinations to shift the old data down at the same time. I have manually formatted the Balance Timelines page because I was unsure how to use a table with this so if you have any suggestions I am open to them. Thank you!

Goal:
1. Shift all prior data Balance Timelines worksheet from rows A3-H3 and below by one row for new data to be captured
2. Move data from Balance Entry Sheet to Balance Timelines row A3
3. Retain the preexisting formatting on the destination sheet, Balance Timelines

Here is my current macro:

Sub Tracking()

Dim sourceWs As Worksheet, dstWs As Worksheet


With Range("B3")

Set sourceWs = Sheets("Balance Entry Sheet")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("B3").Copy

Call dstWs.Range("A4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

With Range("B4")

Set sourceWs = Sheets("Balance Entry Sheet")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("B4").Copy

Call dstWs.Range("B4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

End Sub

 

[Jamie McMillan]

Hello Mo,

The code below inserts a row, at row 3 - keeping the formatting from below, then copies the data from the other sheet and pastes the values. (If not paste values it will paste the formatting too.

Sub BalanceTimelines()
    Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow = 0
    Sheets("Balance Entry Sheet").Range("A3:H3").Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Jamie

 

[Mo796]

Hello Mo,

The code below inserts a row, at row 3 - keeping the formatting from below, then copies the data from the other sheet and pastes the values. (If not paste values it will paste the formatting too.

Sub BalanceTimelines()
    Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow = 0
    Sheets("Balance Entry Sheet").Range("A3:H3").Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Jamie

Hi Jamie,

Thank you very much for your reply, although I am not sure if I made my intentions clear enough as the code didn't quite achieve what I need. Sorry, I am self-taught at this. I already have data entered into my Balance Timelines worksheet in columns A3:H3 and several rows below. I want to move all the current data from rows A3:H3 and below down one row to make room to copy over the new data from my other worksheet, Balance Entry Sheet, rows B4:B8. My current code already successfully copies the new data from one worksheet to the other, but it overwrites the data I already have there. I am trying to track the data that is captured from my source worksheet over time but I want the newest entries to always filter to the top of the list.

 

[Jamie McMillan]

Hello Mo,

Then you just need the first line.

Sub OneLine()
    Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow = 0
End Sub

Assuming your 'Macro Button' in on Balance Timelines, if not

Sub OneLine()
    Sheets("Balance Timelines").select
    Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow = 0
End Sub

Then run your other code.

Jamie

 

[Mo796]

Hello Mo,

Then you just need the first line.

Sub OneLine()
    Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow = 0
End Sub

Assuming your 'Macro Button' in on Balance Timelines, if not

Sub OneLine()
    Sheets("Balance Timelines").select
    Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow = 0
End Sub

Then run your other code.

Jamie

Wow, so simple, that worked. Thank you so much!!