Copy 2 selections and headers for each range

[Robie]

Hi

I have an output that should looks like this:

49.png

I can (obviously) do the above by hand, i.e. copy the range from another sheet starting at the correct row number. The two output are variable in terms of *number of rows*. Each output has an header as shown in the picture.
Now, how can do this using VBA? Under VBA, I can insert the 1st header and the 1st range (because I know the 1st header row is 1 and 1st range start row is 2) but how do I insert 2nd header and then the 2nd range where I don't know the starting row? This is how far I have got with the VBA code.
Quite simply, how can I make this statement Range("A2").Select to select the 'n' row after the 1st range (where I can insert the 2nd header and the 2nd range)?
Hope this makes sense.
Thanks.

Code: Select all

Sub WeeklyReport()
'
' WeeklyReport Macro
' Display the Weekly Report, i.e. released last week and this week.
'
    Dim lastCol, lastRow As Long
    
    ClearSetFilters
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("pcStatus")
        .PivotItems("Released").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "WebQuery.requestedReleaseDate").PivotFilters.Add Type:=xlDateLastWeek
    lastRow = CopyContent4Pasting
    Sheets("WeeklyReport").Select
    Range("A2").Select
    ActiveSheet.Paste

 
'    ThisWeek
End Sub

[HansV]

You could use a line like this to select the cell two rows below the last used row in column A:

Code: Select all

    Range("A" & Rows.Count).End(xlUp).Offset(2).Select

[Robie]

You could use a line like this to select the cell two rows below the last used row in column A:

Code: Select all

    Range("A" & Rows.Count).End(xlUp).Offset(2).Select

Thanks Hans.
How do I expand that selection to include say column 'I'?

[HansV]

Sorry, I don't understand. Your current code has

Code: Select all

    Range("A2").Select
    ActiveSheet.Paste

After changing the pivot table filter to this week, copying the table and switching to the target sheet, you can use

Code: Select all

    Range("A" & Rows.Count).End(xlUp).Offset(2).Select
    ActiveSheet.Paste

Why would you need to "include say column I"?

[Robie]

Sorry, I don't understand. Your current code has

Code: Select all

    Range("A2").Select
    ActiveSheet.Paste

After changing the pivot table filter to this week, copying the table and switching to the target sheet, you can use

Code: Select all

    Range("A" & Rows.Count).End(xlUp).Offset(2).Select
    ActiveSheet.Paste

Why would you need to "include say column I"?

Thanks Hans. Sorry. Yes, I can then copy the range to the newly selected cell. I don't need to worry about selecting the whole columns up I as the range copy will copy anyway.

Thanks Hans. All is well.