-
Help Defining variables
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.
Last edited by Aussiebear; 02-27-2012 at 02:48 PM.
Reason: adjusted the tags to the correct usage
-
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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]
-
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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.
-
Variables can be reassigned. Depending upon your code, you can loop through Workbooks/Worksheets running your code on each as required.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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!
-
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules