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

KimC33

New Member
Joined
Aug 31, 2023
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Sample mr excel 1.jpg
    Sample mr excel 1.jpg
    148.6 KB · Views: 17
  • Sample mr excel 2.jpg
    Sample mr excel 2.jpg
    216.8 KB · Views: 16
  • Sample mr excel 3.jpg
    Sample mr excel 3.jpg
    206.9 KB · Views: 14

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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

VBA Code:
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,703
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top