Search code examples
excelvbarangecopy-paste

VBA Set Pasted Rows as Range


Below is part of the code to copy a range from sheet "Template" and paste to the first blank Row on the Active Sheet. Row 1 is the Header Row.

What I am wanting to do is reference the just pasted rows in order to then Group the Rows.

My VBA is poor and I am unsure how to correctly set "PastedRange". How could I achieve this?

    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim LRow As Long
    Dim PastedRange As Range

    Set copySheet = ThisWorkbook.Worksheets("Template")
    Set pasteSheet = ThisWorkbook.ActiveSheet
    Set PastedRange = .Range("A" & .Rows.Count).End(xlUp).Row
    
    With pasteSheet
        '~~> Find the last cell to write to
        If Application.WorksheetFunction.CountA(.Cells) = 0 Then
            LRow = 2
        Else
            LRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        End If

        copySheet.Range("2:" & copySheet.Cells(Rows.Count, _ 
        1).End(xlUp).Row).Copy
        .Rows(LRow).PasteSpecial Paste:=xlPasteAll
        PastedRange.Group
    End With

Solution

  • Please, try the next code. It will group all pasted rows, except the first one:

    Sub testGroupRows()
       Dim copySheet As Worksheet, pasteSheet As Worksheet, LRow As Long, csLastRow As Long
    
        Set copySheet = ThisWorkbook.Worksheets("Template")
        Set pasteSheet = ThisWorkbook.ActiveSheet
        
        With pasteSheet
            '~~> Find the last cell to write to
            If Application.WorksheetFunction.CountA(.cells) = 0 Then
                LRow = 2
            Else
                LRow = .Range("A" & .rows.count).End(xlUp).row + 1
            End If
            'if the summary row is not above, set it to be above:
            If .Outline.SummaryRow <> xlSummaryAbove Then .Outline.SummaryRow = xlSummaryAbove
            
            csLastRow = copySheet.cells(rows.count, 1).End(xlUp).row
            copySheet.Range("2:" & csLastRow).Copy
            .rows(LRow).PasteSpecial Paste:=xlPasteAll
            
            'group skipping the first copied row:
            .Range(.cells(.rows.count, 1).End(xlUp), .cells(.rows.count, 1).End _
                                           (xlUp).Offset(-(csLastRow - 3), 1)).EntireRow.Group
        End With
    End Sub