1

I am wondering how I can increase a selection range by 1 row each time the code is run but for it to always increase selection by 1.

What happens is I get new data which is pasted into a table and the pivot table is refreshed. Upon refresh, there are new entries in the pivot table. I would like to select a certain range of cells when new data is added and copy this selection to another page (but not all the new data as some of the data is unreliable)

Code so far is:

Sub A_Data_Refresh_Select ()

ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Range("B19:G509").Select

I know this just simply selects the range, but it was just done using a macro so I could edit it. Ideally I would like to select all the data apart from the last 2 or 3 rows each time. Maybe that is an easier way to think of it?

Thanks for the help.

XLchonker
  • 11
  • 1
  • 1
    Possibly useful: [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). You can use `Offset` to exclude the last row(s) when selecting, or possibly `Resize`. – BigBen Jul 19 '21 at 13:40
  • 1
    But why do you need selecting the range? Usually, selecting it is a bad habit in VBA... – FaneDuru Jul 19 '21 at 13:42
  • well technically I need to copy a range of values from a pivot table to a new sheet. I only used select as a starting point. But thanks I will also check out the offset command BigBen has posted. – XLchonker Jul 19 '21 at 13:56
  • No need to `Select` before copying. ex.: `Range("B19:G509").Copy` – cybernetic.nomad Jul 19 '21 at 14:59
  • Could you post a screenshot of what you've got and another one of (the result) what you want? What does *increase selection by 1* mean? By one row or column? What exactly is the *certain range*? What does *the last 2 or 3 rows* mean? And what does *each time* mean? Please be more specific and [edit](https://stackoverflow.com/posts/68441478/edit) your post by adding this information. – VBasic2008 Jul 19 '21 at 15:03

0 Answers0