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

 

[KimC33]

I'm so sorry for not responding sooner, this is a lower priority work item so it got pushed to the back burner for a while.

Trying to follow your code for its a little above my head. One thing i see may be an issue is that all three example images are all on the same tab, the Product Metrics tab in my workbook, so the set commands don't appear to be working.

That's the complication, copying a set of data from the top of the tab (pic 1), inserting it into the range table in the middle of the tab (bottom of left table pic 2) while not messing up the sums for that table and a pivot of that table that follows it (pic 3). Each month this data will be copied down so the lines that will be inserted will vary by the 14 rows. And you are correct, that was a typo, the data that is being copied is A5:J18. I am creating a new file for 2025 so the history will revert back to one month's data, but as it grows, where i insert the data will change too.

For Example - Jan's data will be pasted into row 202-215, so Feb will be inserted starting at row 216, and March row 230. This could change though if any projects get added or deleted during the course of the year. If we stop tracking one, the number of lines to be copied will be reduced. that's why need to be able to have a variable code for this but cannot figure it out myself.

Thank you so much for your help!