Select and copy range based on value "Closing Balance"

[viper56]

I am trying to do a few things with this code, some of which I have sorted out already. My issue is trying to select a range, and having it end on a specific cell within the table I want to select.

Step 1 - find letter "A" in column E, then go down 6 cells and left 1 to "Opening Balance" in column D (it will always be 6 down, 1 left to go from this value "A" to "Opening Balance").

Step 2- once at "Opening Balance" in column D, I need to select the entire range down to "Closing Balance" (this range will differ depending on amount of info). In the example attached I need to select "A22:P30".

Step 3 - copy and paste this data into another tab (I am fine with this code).

Thanks for any input!

Please Login or Register  to view this content.

[protonLeah]

Please Login or Register  to view this content.

[luke.guthrie1]

You are amazing. Is there any way to get this to select the table from cell A instead of D?

[protonLeah]

Look for "A" in column E:
With Range("E:E")
Set Opening = .Find(What:="A")
End With

Use offset to re-initialize OPENING to the cell down 6 rows and LEFT one column to column D:

Set Opening = Opening.Offset(6, -1)

With Range("D:D")
Set Closing = .Find(What:="Closing Balance")
End With

Since you now have the location of the "Closing Balance" you can reset OPENING to reference column A of the row where Opening Balance is found, by using OFFSET again:

Set Opening = Opening.Offset(ColumnOffset:=-3)

But now you must resize the range from 13 columns to 15 (i.e., A:P)

Range(Opening.Address, Closing.Address).Resize(columnsize:=15).Select