Selecting a dynamic range of cells

Question

I'm automating a weekly report which does some repetitive work to scrub and cleanup a data set. At the end of the scrubbing process I run several reports.

On the last report I'm aggregating data in a Pivot Table on a worksheet called 'Pivot". I need to select a dynamic range to generate a line chart on a worksheet called 'Chart'.

I'm having trouble with the dynamic range selection. The chart part I'm comfortable with creating.

My Pivot table looks something like this;

 |    A   | B | C | D | E | F | G | H | I | J
1|        |   |   |   |   |   |   |   |   |
2|        |Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Total
3|Duration| 3 | 3 | 2 | 1 | 2 | 3 | 4 | 5 | 23

The data set includes 2017, 2018, and 2019 data.

I filter on only 2019 data. We only want to create a chart on last 6 months. So if I'm running the report in August, I want the script to Select I2:D2, and copy to the clipboard so I can paste to the 'Chart' worksheet to generate my line chart.

Here's my challenges;

1st Challenge - I need to select current month (I2) going back 6 Months (D2).

2nd Challenge - Let's say I ran the report in March (D3), the script should only select (D3:B3)

I figured out how to find the cell for the current month, select it, then add the address to a variable.

MyMonth = MonthName(Month(Date)) ' In this case: August

    With Worksheets("Pivot").Cells
        Set cellFound = .Find(MyMonth, LookIn:=xlValues)

        If Not cellFound Is Nothing Then
            cellFound.Select ' Selects I2
        End If
    End With

The above does what I expect, finds the cell for the current month, selects the cell, then adds the address ($I$2) to a variable. I have no clue about where to go from here.

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2019-08-27T06:15:31

Try this:

1) Change the VBA name of the "Pivot" sheet to "Pivot"

2) Copy paste this code to a module and step through it to see how it works.

Sub SelectCells()

    Dim searchRange As Range
    Dim cellFound As Range

    Dim currentMonth As Single
    Dim firstMonth As Single
    Dim pastMonths As Single

    pastMonths = 6

    currentMonth = Month(Date) ' In this case: August
    firstMonth = WorksheetFunction.Max(currentMonth - pastMonths, 1)

    ' Select current month going back 6 months
    Pivot.Range("B3").Offset(0, firstMonth - 1).Select
    Pivot.Range("B3").Offset(0, currentMonth - 1).Select

    Pivot.Range(Pivot.Range("B3").Offset(0, firstMonth - 1), Pivot.Range("B3").Offset(0, currentMonth - 1)).Select

End Sub

Some suggestions: a. Avoid selecting cells (I'm only showing you a demonstration) b. When creating the chart, set the source to the original range, instead of creating a copy

Note: if this helps, remember to mark the question so others can find them.

Selecting a dynamic range of cells

1 Answers