Select until last row

Marcia · #1 08-12-2022, 01:28 AM

Hi. This code selects the range A5 to AQ50. I would like the code to select only the rows from A5 until the last row of a dynamic pivot table.

Code:

Dim lr As Long
    Range("A5:AQ5" & lr).Select
    Selection.Copy

What should the line be?
Thank you.

p45cal · #2 08-12-2022, 04:05 AM

We need to know a bit more about the pivot table.
Best attach a (mockup of) the workbook with what you want selected highlighted, also a bit of info on how dynamic the pivot will be (row numbers, column numbers, compact view perhaps etc.)

Marcia · #3 08-12-2022, 08:09 AM

Here is a mock up data.

p45cal · #4 08-12-2022, 10:48 AM

In the attached a macro:

Code:

Sub test()
Set PT = Sheets("Sum").PivotTables(1)
PT.TableRange1.Copy
Sheets("SumA").Range("F4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub

Separately, a copy of your pivot table at cell B15 of the SumA sheet with a small adjustment to how things are displayed.

Marcia · #5 08-12-2022, 03:54 PM

It's perfect p45Cal
I did not know that the field lists in the Values section could also be the field lists in the Rows section. I thought that a field should only be placed in either Filter, Row or Column.
Thank you..

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Select entire text and then de-select certain Headings?	tq94	Word	3	02-26-2022 03:47 AM
select paragraphs	yacov	Word	2	10-25-2020 02:53 AM
How to select the end of a document?	ArtKilp	Word VBA	2	06-15-2020 06:44 PM
Select a row in a different tab	cosmicyes	Excel Programming	1	07-09-2018 02:47 AM
How to select row	aymanharake	Excel Programming	3	02-18-2017 04:09 PM