Insert Multiple Column Named Range

[chlearning]

Good day.
I have a workbook which contains several named worksheets. On the "Options 10-8-19" worksheet I've created dynamic named ranges for 2 sets of data. I've also established two count cells for the number of rows in range. I'm looking for a way to insert the ranges into another worksheet in the same workbook. The ranges in "Options 10-8-19" start at row $O$3 (to $U$3) and can extend as far down as $O$26, and $O$33 (to $U$33) and can extend as far down as $O$58. I've got the counts in $W3 and $W33, respectively. The dynamic named ranges are Summerrangetest (3-26) and Winterrangetest (33-58). They will be going on worksheets Summers Quotes and Winter Quotes. The rows will need to be inserted starting at B16 (to H16) and down. I've tried to come at this from several directions and can't make it work. Can someone please help? If I can get one to work, I'm sure I can figure out the other. Thanks!

 

[FatBoyClam]

You should be able to do this with VBA. Open the VBA editor (press Alt+F11 or go to the Developer tab on the ribbon and click "Visual Basic"). Insert a module (right-click the VBAProject in the tree view on the left and select Insert>Module), and then paste the following code into the blank editor window.

Sub CopyRanges()

Dim rngSource As Range, rngDest As Range, i As Integer, strSeason As String

For i = 0 To 1
    
    strSeason = "Summer"
    
    If i = 1 Then strSeason = "Winter"

    Set rngSource = Sheets("Options 10-8-19").Range(strSeason & "rangetest")
    
    Set rngDest = Sheets(strSeason & "s Quotes").Range("B16")
    
    Set rngDest = rngDest.Resize(rngSource.Rows.Count, rngSource.Columns.Count)
    
    rngDest.Value = rngSource.Value
    
Next i


End Sub

This relies on the consistency of your naming convention (the two ranges are [Season]rangetest and the two target sheets are [Season]s Quotes) so it uses a loop to repeat the action for the two range/sheets. In your description your target sheets are "Summers Quotes" and "Winter Quotes". If you can add the "s" to "Winter" in the sheet name to get the consistency, this should work. Otherwise it'd need a bit of a tweak.

This will copy the contents of the whole of each range to a range of matching size on the target sheet.

It does not copy formulas or formats, just values. If you want to copy more than just values, or you want it to do anything else, let me know.

 

[chlearning]

FatBoyClam...
It works great at pulling the ranges, but it isn't moving the rest of my rows down on the Summer (I renamed, thanks) and Winter Quotes pages. Any suggestions?

 

[FatBoyClam]

Ah, sorry - I didn't realise there was already data on the Summer/Winter sheets.

Sub InsertRanges()

Dim rngSource As Range, rngDest As Range, i As Integer, strSeason As String

For i = 0 To 1
    
    strSeason = "Summer"
    
    If i = 1 Then strSeason = "Winter"

    Set rngSource = Sheets("Options 10-8-19").Range(strSeason & "rangetest")
    
    Set rngDest = Sheets(strSeason & "s Quotes").Range("B16")
        
    Set rngDest = rngDest.Resize(rngSource.Rows.Count, rngSource.Columns.Count)
    
    rngDest.Insert xlDown
    
    Set rngDest = rngDest.Offset(-rngDest.Rows.Count, 0)
    
    rngDest.Value = rngSource.Value
    
Next i


End Sub

This will push the cells in B16:H16 (and below) down by the number of rows in the source range. If you have data other columns for those rows, and you want to insert entire new rows to stop that losing it's alignment, try this;

Sub InsertRanges()

Dim rngSource As Range, rngDest As Range, i As Integer, strSeason As String

For i = 0 To 1
    
    strSeason = "Summer"
    
    If i = 1 Then strSeason = "Winter"

    Set rngSource = Sheets("Options 10-8-19").Range(strSeason & "rangetest")
    
    Set rngDest = Sheets(strSeason & "s Quotes").Range("B16")
        
    Set rngDest = rngDest.Resize(rngSource.Rows.Count, rngSource.Columns.Count)
    
    rngDest.EntireRow.Insert xlDown
    
    Set rngDest = rngDest.Offset(-rngDest.Rows.Count, 0)
    
    rngDest.Value = rngSource.Value
    
Next i


End Sub

 

[chlearning]

So, that would have worked great had some of the cells below not been merged. Is there a way to insert these and copy the formatting from the named range, or should I just update my summer and winter quote sheets to avoid the issue? Thanks so much for all of your help on this.

 

[chlearning]

The format of the named ranges is the same format that is in B16. If that helps.

 

[FatBoyClam]

Merged cells are a bugger!

I presume that the merge extends beyond the target range (i.e. merged from column G to Column J)

Can you live without having them Merged?

If not, is inserting entire rows OK? That should happily move the merged cells down.

Assuming you can go ahead with inserting entire rows, this should copy formats as well

Sub InsertRanges()

Dim rngSource As Range, rngDest As Range, i As Integer, strSeason As String

For i = 0 To 1
    
    strSeason = "Summer"
    
    If i = 1 Then strSeason = "Winter"

    Set rngSource = Sheets("Options 10-8-19").Range(strSeason & "rangetest")
    
    Set rngDest = Sheets(strSeason & "s Quotes").Range("B16")
        
    Set rngDest = rngDest.Resize(rngSource.Rows.Count, rngSource.Columns.Count)
    
    rngDest.EntireRow.Insert xlDown
            
    Set rngDest = rngDest.Offset(-rngDest.Rows.Count, 0)
    
    rngSource.Copy
    
    rngDest.PasteSpecial xlPasteFormats
    rngDest.PasteSpecial xlPasteValues
    
    
Next i


End Sub

 

[chlearning]

Entire rows are great! And this shifted everything...but it inserted all of the rows the dynamic range is based on, not just the ones containing a value. Before it inserted rows, it wasn't adding the additional ones. Thanks again for all your help. We're almost there....

 

[FatBoyClam]

Yes - it will insert the number of rows the dynamic range contains. This is what you asked for.

What is dynamic about the named ranges if not only containing the rows that have values in them?

 

[chlearning]

Yes - it will insert the number of rows the dynamic range contains. This is what you asked for.

What is dynamic about the named ranges if not only containing the rows that have values in them?

But it is entering all 24 rows from the $0$3-$U$26 area, not just the 2 that currently contain information. Maybe it's a problem with my named range. I will investigate further. Thank you for all your help.