VBA typed range in a cell - Applying to a different worksheet

[nmbc99]

Hello I have a weird thing going on with some practice code I'm trying. I am trying to create a code that copies the value of a cell, that has a range in it (@Rick Rothstein for helping with that). That works. I then want to use that value (the range) and select said range in a different worksheet. The below code does that.

Public Sub Blah()
Dim rng As Range

'MsgBox Cells(2, 1).Value
Sheet2.Activate
Set rng = Range(Sheet2.Cells(1, 1).Value)


Sheet1.Activate
rng.Select
rng.Interior.Color = RGB(255, 0, 0)

'MsgBox rng
End Sub

However, the code below is Actually what I need, and now the code throws an error at the select function? Why is it doing that??? The only think I changed was the order of the sheets? Also, does anyone know how to fix this???

Public Sub Blah()
Dim rng As Range

'MsgBox Cells(2, 1).Value
Sheet1.Activate
Set rng = Range(Sheet1.Cells(1, 1).Value)


Sheet2.Activate
rng.Select
rng.Interior.Color = RGB(255, 0, 0)

'MsgBox rng
End Sub

Just FYI the cell in A1 on both Sheet 2 and Sheet 1 are exactly the same

 

[Fluff]

What exactly is in A1?

 

[nmbc99]

What exactly is in A1?

It's a range : A1 = D1:K42

 

[Fluff]

In that case that is what the problem is, your range is set on sheet1 & then you try to select it when Sheet2 is active, which you cannot do.
Try replacing all the code with

Public Sub Blah()
Sheet2.Range(Sheet1.Cells(1, 1).Value).Interior.Color = RGB(255, 0, 0)

End Sub

 

[nmbc99]

In that case that is what the problem is, your range is set on sheet1 & then you try to select it when Sheet2 is active, which you cannot do.
Try replacing all the code with

Public Sub Blah()
Sheet2.Range(Sheet1.Cells(1, 1).Value).Interior.Color = RGB(255, 0, 0)

End Sub

Thank you for the suggestion, but that doesn't quite fix my issue, because even if I do the following it still throws an error:

Public Sub Blah()
Dim rng As Range

'MsgBox Cells(2, 1).Value
Sheet2.Activate
Set rng = Range(Sheet2.Cells(2, 1).Value)


rng.Select
rng.Interior.Color = RGB(255, 0, 0)

'MsgBox rng
End Sub

I am trying out this code because in a much more expansive code I made, I've been having users select their range to do an .Autofilter. However, they have to do this EVERYTIME, so someone asked if I could make it a one time thing. So I am trying out this code so that a user can just type in the range of the data they will need to select so that the range can be selected by looking at the cell (in this example A1) they type their range into.

For example - please look at the picture I uploaded
The code will copy the range typed into Cell A1, then the autofilter will run using the range given in A1 (ideally assigned to a range variable)

 

[Fluff]

That code is looking at A2 not A1.

 

[nmbc99]

That code is looking at A2 not A1.

Sorry I had already corrected the positioning to A1, and forgot to reflect that in the code. So even after I changed Sheet2.Cells(2, 1).Value to Sheet2.Cells(1, 1).Value I still get an error

It seems like unless the selection is in Sheet 1 it doesn't work. Even if everything in the code is saying to look at Sheet2 alone. It keeps throwing me a "Run Time error 1004" - Application-defined or object-defined error"

 

[Fluff]

Did you try the code I posted?
There is very rarely any need to select anything.

 

[nmbc99]

Did you try the code I posted?
There is very rarely any need to select anything.

Sorry, no your code didn't work but I just realized why. My code is in worksheets rather than modules or this workbooks . Idk how I forgot that.

With that being said, putting all the code into the workbook fixes everything. Unfortunately, the rather large code I was prepping this code for is also in a worksheet. I think I need to create a new post about it because that potentially going to be a monster to correct

 

[Fluff]

Glad you sorted it & thanks fro the feedback.