Macro help - add data from a tab to the bottom of the existing data

[jimbogarner]

Hi,

Ive created a macro in a workbook that takes data from a tab 'Latest data' and adds it to another tab, however, the following month when that data gets replaced with the following months 'latest data', I want that macro to add the new data to the bottom of what was added before - it currently just overwrites what was there previously?

This is the macro that I recorded;

Sub Run_Rec()
'
' Run_Rec Macro
'

'
Sheets("Latest Data").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("1:59").Select
Selection.Copy
Sheets("B&G SAP DL").Select
ActiveWindow.SmallScroll Down:=15
Range("A70").Select
ActiveSheet.Paste
Range("F69").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("F69:F128")
Range("F69:F128").Select
ActiveWindow.SmallScroll Down:=12
Range("J70").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("Q70").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("Q70"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
ActiveWindow.SmallScroll Down:=-12
Range("R70").Select
Sheets("B&G").Select
Range("B23").Select
ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"\\ni.ad.newsint\SharedData\Central\PetGroup\petcommon\PETERB\Wireless Group\9. Month End\FY22\6. December\B&G\BS Recs\[Accruals - B&G - Dec 21.xlsx]B&G SAP DL!R1C1:R128C19" _
, Version:=xlPivotTableVersion15)
Range("C19").Select
Calculate
Sheets("B&G SAP DL").Select
Range("F105").Select
Sheets("B&G").Select
Range("C22").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
End Sub

Thanks,

James

 

[Alex Blakenburg]

Sorry, next step of the macro... once the data has pasted in, I want it to then copy down the formulas in Column's F & Q-S from the 2nd screenshot above to the bottom of the new text that has been added?

I will have a look tomorrow, login off for the day.

 

[Alex Blakenburg]

Sorry, next step of the macro... once the data has pasted in, I want it to then copy down the formulas in Column's F & Q-S from the 2nd screenshot above to the bottom of the new text that has been added?

See if this does what you need:
(do a find for XXX for the changes)

Copying the formula for F is a little bit odd since F is one of columns that is copied in from "Latest Data"

Sub AppendLatest_V02()

    Dim rgSource As Range, rgDest As Range
    Dim shSource As Worksheet, shDest As Worksheet
    ' XXX Added
    Dim lastRow As Long, newlastRow As Long
    
    Set shSource = Worksheets("Latest Data")
    Set shDest = Worksheets("B&G SAP DL")
    
    With shSource.Range("A1").CurrentRegion
        Set rgSource = .Offset(1).Resize(.Rows.Count - 1)
    End With
    
    ' XXX Added
    lastRow = shDest.Cells(Rows.Count, "A").End(xlUp).Row
    ' XXX Changed to use above
    Set rgDest = shDest.Cells(lastRow + 1, "A")
    
    rgSource.Copy Destination:=rgDest
    
    ' XXX Added - Fill down formulas
    newlastRow = shDest.Cells(Rows.Count, "A").End(xlUp).Row
    shDest.Range("F" & lastRow & ":F" & newlastRow).FillDown
    shDest.Range("Q" & lastRow & ":S" & newlastRow).FillDown

End Sub

 

[jimbogarner]

sorry bumped this but the latest posts hadn't refreshed!

 

[jimbogarner]

That worked perfectly thanks very much

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.