Insert Cells into Defined Range in middle of sheet surrounded with data

[KimC33]

In my spreadsheet, I have a set of data at the top (14 lines (A5:J56)) "Current Month". I need to copy this data and insert it in a defined range "Full History" starting in row 46. Its basically monthly data that I am copying down so each month it will be another 14 lines down since the last time the macro was run. There is data after this defined range too including a pivot table. I also have data on the right side so cannot insert rows. How do you write the code to insert the cells in the range (A5:J56) at the end of the range B47:L102? And then at the end of that range, each month going forward? Dont see how to attach the sample Excel file so here are a few screenshots of the sample file for your reference. thanks for the help.

 

[sijpie]

I am assuming that you made a typo in your story, and that the input range to be copied to the history sheet is A5:J18

Option Explicit


'//// Macro to copy current month to the Full History sheet, appending to history data \\\\
Sub TransferMonth()
    Dim wsMonth As Worksheet, wsHist As Worksheet
    Dim lRin As Long, lRout As Long
    Dim rIn As Range, rOut As Range
    
    ' to make maintenance easy,have some worksheet variables set to the two sheets
    Set wsMonth = Sheets("Current Month")
    Set wsHist = Sheets("Full History")
    
    ' set the input range to theinput range on wsMonth
    With wsMonth.Range("A4").CurrentRegion
        Set rIn = wsMonth.Range("A5").Resize(.Rows.Count - 1, .Columns.Count)
    End With
    
    ' set the output range to the end of the range starting at B46 on the full history sheet
    lRout = wsHist.Range("B46").End(xlDown).Row + 1
    Set rOut = wsHist.Range("B" & lRout).Resize(rIn.Rows.Count, rIn.Columns.Count)
    
    ' Now copy the values from the input range to the history output range
    rOut.Value = rIn.Value
    
End Sub