Using a Range Variable

Question

I am having issues when trying to use a Range as a variable. Keep getting the "Select Method of Range Class Failed" error. Any idea what I might be doing wrong here?

Dim OnRent As Workbook
Dim MattFile As Workbook
Dim Rng As Range


Set OnRent = Workbooks("On-Rent 09-22-17.xlsx")
Set MattFile = ThisWorkbook
Set Rng = ActiveSheet.Range("B101")



OnRent.Activate
ActiveSheet.PivotTables("PivotTable1").PivotFields("CHKOUT_POOL"). _
    ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("CHKOUT_POOL").CurrentPage _
    = "LOS ANGELES"
Range("B15:L108").Copy
MattFile.Activate
Sheets("LAX Data").Activate
Rng.Select
ActiveSheet.Paste

kolcinx · Accepted Answer · 2017-09-25 18:00:24Z

1

Problem: Rng.Select only works, if the Rng.Parent is active

The Range.Select method will only work, if the sheet, where the range belongs, is active.

The Rng range may not be on the Sheets("LAX Data") and by calling Rng.Select while another sheet is active, you run into error.

To further explain:

You set the range to
Set Rng = ActiveSheet.Range("B101") 'Note: this can be any sheet. The range object stores its position along with the sheet it is from, as a file is defined by a full/absolute path.

Later, when you want to Rng.Select you are (can be) on different sheet and the select will not work.

Do not use Range.Select and Sheet.Activate methods if you dont have to

See Mats link! Or some of the MANY MANY advice on the net like Power Excel vba secret, avoid using select

Some code alternative

To demonstrate how this can work, here is some untested code.

Sub refreshOnRentForLosAngeles()
    Dim OnRentSheetWithPivots As Worksheet
    Dim Pivot As PivotTable

    Set OnRentSheetWithPivots = Workbooks("On-Rent 09-22-17.xlsx").Sheets(1) 'change to fit
    Set Pivot = OnRentSheetWithPivots.PivotTables("PivotTable1")
    Pivot.PivotFields("CHKOUT_POOL").ClearAllFilters
    Pivot.PivotFields("CHKOUT_POOL").CurrentPage = "LOS ANGELES" 'Turn the city into input variable for reusability?

    'The copy source will always be the same? Can u make it dynamic?
    OnRentSheetWithPivots.Range("B15:L108").Copy ThisWorkbook.Sheets("LAX Data").Range("B101")
End Sub

edited Sep 25, 2017 at 18:00

answered Sep 25, 2017 at 17:32

kolcinx

2,2431 gold badge17 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mpthomas1219 Over a year ago

Thanks for the feedback. So even though I am activating the sheet ahead of selecting the Rng cell, it still doesn't know where to put it? Sounds like the best bet is to keep doing what I am doing and just find and replace the Range every day. The issue is, I am updating a report daily that is pasting the data from one report to a separate one using 17 different tabs, so that same code above is repeated 17 times, but for different Data tabs. Figured I would try and find an easy way to change the range output everyday instead of finding and replacing every time.

kolcinx Over a year ago

@mpthomas1219 1) What you are missing is this: Rng variable is stroring The cells. On The sheet. Only those. It's an object, not a cell address. (read multiple times). 2) Dont find and replace hard coded values. Make them variables. For ex. you can make a dropdowns of the city names and the target sheets as well. Then you just select what you need and press a button. 3) If the paste (destination) cell is always B101 on every sheet, you can use this string as an address and combine it with sheet, to make a range. Even better, make a sheet scoped Named range and use that.

Kresimir L. · Accepted Answer · 2017-09-25 17:39:26Z

0

If you want to reference range "B101" on sheet "LAX Data", you should replace Rng.Select with Range("B101").Select. this way you will not try to select range which is not on ActiveSheet (which is the reason for an error).

answered Sep 25, 2017 at 17:39

Kresimir L.

2,4412 gold badges12 silver badges22 bronze badges

1 Comment

mpthomas1219 Over a year ago

Thanks, but I was trying to avoid having to do that. That is how I have it now, but I am going through 17 different sheets, so I was hoping to only change the Range once instead of having to Find and Replace every time.

Collectives™ on Stack Overflow

Using a Range Variable

2 Answers 2

Problem: Rng.Select only works, if the Rng.Parent is active

Do not use Range.Select and Sheet.Activate methods if you dont have to

Some code alternative

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Problem: Rng.Select only works, if the Rng.Parent is active

Do not use Range.Select and Sheet.Activate methods if you dont have to

Some code alternative

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related