VBA Set Pasted Rows as Range

Question

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

you never use the PastedRange and .Range("A" & .Rows.Count).End(xlUp).Row must be included in With something and End With. Also, you assign value to LRow only. What do you want to paste? Please provide your desired sample and corresponding result — GGG
Sorry I am a little confused as to what you are saying can and can't be done. I have edited the question to include what is being copied. The desired result is that the copied range is pasted onto the active sheet into the first blank row and then the rows that were just pasted, to be grouped. — aye cee
No reason to be confused... Set PastedRange = .Range("A" & .Rows.Count).End(xlUp).Row, in the place where it exists, will raise an error. It cannot return anything with that used construction (.Range(...), from two points of view: The line returns a row instead of a range, and .Range(...` has a meaning only inside a With SomeSheet followed by your code and End With. — FaneDuru
I have put PastedRange = .Range("A" & .Rows.Count).End(xlUp) and PastedRange.Group just before End With. Is this the correct way to do it? The grouping hasn't worked. — aye cee
It is correct in terms of not returning an error, but your code should calculate the last pasted rows, not the last row. Please, test the code I pasted and send some feedback. — FaneDuru

FaneDuru FaneDuru · Accepted Answer · 2021-07-26T10:13:10

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

VBA Set Pasted Rows as Range

1 Answers