Help Defining variables

[catharsis50]

I need help defining this variable so that it evaluates when used on any active worksheet. Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Example of how it's used.
Range("AL2").AutoFill Destination:=Range("AL2:AL" & LastRow) or
ActiveWorkbook.Worksheets("Closed Opps w Contacts").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Closed Opps w Contacts").Sort.SortFields.Add Key _
:=Range("T1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Closed Opps w Contacts").Sort
.SetRange Range("A2:T" & LastRow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.RemoveDuplicates Columns:=5, Header:=xlYes

I use it often to capture dynamic ranges of data on different worksheets and different workbooks throughout my macro. Thanks for the help.

[mdmackillop]

You need to use a Worksheet variable to qualify the range and other sheets.
Dim ws as Worksheet
Set ws = Sheets(1)
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

[catharsis50]

Thanks for the reply. During the code I open up three workbooks and this didn't work when trying to fill in a new workbook. I just tried this though and seems to be working so far.

Dim wks As Worksheet
Set wks = ActiveSheet

[mdmackillop]

It's generally not efficient and can be very confusing to work with ActiveWorkbook and ActiveWorksheet. Create variables and you can manipulate all the books and sheets without Activating or Selecting

Sub Test()
Dim WB1 As Workbook, WB2 As Workbook, WB3 As Workbook
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet

Set WB1 = Workbooks("Book1.xls")
Set WB2 = Workbooks("Book2.xls")
Set WB3 = Workbooks("Book3.xls")

Set WS1 = WB1.Sheets(1)
Set WS2 = WB2.Sheets(2)
Set WS3 = WB3.Sheets(3)

WS1.Range("A1") = "Book1-Sheet1"
WS2.Range("A1") = "Book2-Sheet2"
WS3.Range("A1") = "Book3-Sheet3"

End Sub

[catharsis50]

Would I have to define a variable for every worksheet that I use that variable on for every workbook? There are 18 worksheets in each workbook so I'm trying to have a solution where I don't have to define a variable 56 times if possible.

[mdmackillop]

Variables can be reassigned. Depending upon your code, you can loop through Workbooks/Worksheets running your code on each as required.

[catharsis50]

Would I be telling the code to loop back to the variable definition to be reevaluated then back to the current place in the code to do that? I haven't done anything like that before. If so what commands would be used to achieve this?

Thanks!

[mdmackillop]

Here's an example
Sub Test()

Dim i As Long
Dim arr, a

arr = Array("Book1.xls", "Book2.xls", "Book3.xls")
For Each a In arr

For i = 1 To 3
Call DoSort(Workbooks(a).Sheets(i))
Next i

Next a
End Sub

Sub DoSort(ws As Worksheet)
With ws
.Sort.SortFields.Clear
.Sort.SortFields.Add Key _
:=Range("T1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With .Sort
.SetRange Range("A2:T" & LastRow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub