Forum Discussion
Solved
VBA Squared Bracket Worksheet Reference
Hi Everyone, I'm trying to figure out why this code runs when a button is placed on the active SalesData sheet but not when I move the button to an alternative dashboard sheet. I've been using pre...
In the line
Set FilterRange = [SalesData].Range("A1:Q" & Cells(Rows.Count, 1).End(xlUp).Row)Cells and Rows refer to the active sheet, so if that is a different sheet than SalesData, Cells(Rows.Count, 1).End(xlUp).Row will not return the correct value. Does it work if you change the above line to
With [SalesData] Set FilterRange = .Range("A1:Q" & .Cells(.Rows.Count, 1).End(xlUp).Row) End WithNote the . before Range, Cells and Rows.
In the line
Set FilterRange = [SalesData].Range("A1:Q" & Cells(Rows.Count, 1).End(xlUp).Row)
Cells and Rows refer to the active sheet, so if that is a different sheet than SalesData, Cells(Rows.Count, 1).End(xlUp).Row will not return the correct value. Does it work if you change the above line to
With [SalesData]
Set FilterRange = .Range("A1:Q" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With
Note the . before Range, Cells and Rows.
- This works perfectly! I didn't know that was how it should be typed when referring to another sheet, I thought because I set FilterRange with reference to [SalesData] it would know that I was referring to that specific sheet. Should be an easy fix to change my other subs. Thank you so much!
