Help Defining variables

[catharsis50]

I need help defining this variable so that it evaluates when used on any active worksheet. [vba]Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row[/vba]
Example of how it's used.
[vba]Range("AL2").AutoFill Destination:=Range("AL2:AL" & LastRow) [/vba] or
[vba]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
[/vba]
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.
[vba]Dim ws as Worksheet
Set ws = Sheets(1)
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row[/vba]

[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.

[vba]Dim wks As Worksheet
Set wks = ActiveSheet[/vba]

[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

[VBA]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
[/VBA]

[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
[VBA]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[/VBA]