Add rows to worksheet based on size of another data range

[bhall]

I'm trying to automatically add rows to WS1 based on the number of populated rows in range F1:F20 in WS2. for example WS1 (column D) looks like:

1.1.1.3
1.1.1.4
1.1.1.5
etc.

WS2 F1:F20 looks like:
PM
EE
SW
ME
etc.

Basically, I want to automatically add rows below each WBS item in WS1 that contain the labor categories in WS2. I'm okay with VBA solutions (which I believe this requires), I just have a pretty quick turn on getting this done.

Thanks in advance for the help!

 

[Boswell]

This should work. It assumes both ranges contain contiguous ranges of values.

Public Sub BuildReport()

    ' set sheet names and range
    Const DEST_SHEET = "Sheet1"
    Const DEST_RANGE = "F1:F20"
    Const SOURCE_SHEET = "Sheet2"
    Const SOURCE_RANGE = "F1:F20"

    
    With ThisWorkbook
        ' get full ranges
        Dim DestRange As Range: Set DestRange = .Worksheets(DEST_SHEET).Range(DEST_RANGE)
        Dim SourceRange As Range: Set SourceRange = .Worksheets(SOURCE_SHEET).Range(SOURCE_RANGE)
        'subset ranges to only those with values (probably an easier way to do this with xldown)
        Set DestRange = DestRange.Cells(1).Resize(Application.WorksheetFunction.CountA(DestRange))
        Set SourceRange = SourceRange.Cells(1).Resize(Application.WorksheetFunction.CountA(SourceRange))
    End With
    
    ' do the insert
    Dim i As Integer
    For i = DestRange.Cells.Count To 1 Step -1
    
        Dim ShiftRange As Range: Set ShiftRange = DestRange.Cells(i).Offset(1, 0).Resize(SourceRange.Cells.Count)
        ShiftRange.EntireRow.Insert Shift:=xlDown
        ShiftRange.Offset(-ShiftRange.Cells.Count, 0).Value = SourceRange.Value
        
    Next
    

End Sub

 

[bhall]

Works Perfect! Thank you.