Copy sheet to another WB and macro copies that data to last row of master sheet

[nickmax1]

I have the attached sheet that has a "data" sheet which i refer to as a master sheet. I have many spreadsheets to open up and from them need to copy the information held within that are all in a standard template format. I have written some code to help but it is a right mess.

Effectivel I want to be able to open a spreadsheet i need to copy, move/copy the sheet to my workbook and call it "Dump Here", then copy all of it to the last row of my "Data" sheet and then delete the "Dump Here" sheet ready for the next one.

heres what i have so far:

What you need to do is open the test spreadsheet, try and move or copy the sheet to the Master.xlsm (to the end) and I want the macro to rename it (which it does well) and copy the whole thing to the bottom of the "Data" sheet data. but it throws up an error i cant fix.

this one below recognises the new sheet being added and runs the rename macro

Dim m_lngNSheets As Long
 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
     
    MsgBox "New Sheet Added " & Sh.Name
    m_lngNSheets = ThisWorkbook.Sheets.Count
     
End Sub
 
Private Sub Workbook_Open()
    m_lngNSheets = ThisWorkbook.Sheets.Count
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
     
     
    If ThisWorkbook.Sheets.Count > m_lngNSheets Then
        MsgBox "New Sheet Copied or Added " & Sh.Name
        ThisWorkbook.Sheets(Sh.Name).Name = "Dump Here"  ---------- I get an error when trying to copy here
        Call CopyPasteDump             <<<<<<<<<<<<<< think this is where it goes wrong
        
    End If
    m_lngNSheets = ThisWorkbook.Sheets.Count
    
     
End Sub

and this code renames the sheet and does the copying

Sub CopyPasteDump()


    Sheets("Dump Here").Select
    Range("B9:AX9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Data").Select
    
    
    Range("B9").End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
        
    ActiveSheet.Paste
    
   ' Sheets("Dump Here").Select
   'ActiveWindow.SelectedSheets.Delete
    
End Sub