Use Variables for Range in Macro

jjconc · Apr 3rd 2020

The following macro sorts columns A - F based on values in Columns B and D.

I plan to use similar macros for other tabs in the same spreadsheet.

The first row for each tab will be different and will vary as existing rows are deleted and/or new rows are added.

I would like to have the references to A23, B23, D23 and F23 be variables.

This will allow the values to be placed in a cell on each tab.

Having the value in a cell will:

Allow for changes to that value as changes are made to rows on the tab

Eliminate the need to change the macro as changes are made to rows on the tab

Thank you.

Code

Sub BudgetPendingSort()


'


'


'  Sort Pending Debit Tab - Other Transactions


'


ActiveWorkbook.Worksheets("Pending Debit").Sort.SortFields.Clear


ActiveWorkbook.Worksheets("Pending Debit").Sort.SortFields.Add2 _


Key:=Range("B23", Range("B23").End(xlDown)), _


SortOn:=xlSortOnValues, _


Order:=xlAscending, _


DataOption:= _


xlSortNormal


ActiveWorkbook.Worksheets("Pending Debit").Sort.SortFields.Add2 _


Key:=Range("D23", Range("D23").End(xlDown)), _


SortOn:=xlSortOnValues, _


Order:=xlAscending, _


DataOption:= _


xlSortNormal


With ActiveWorkbook.Worksheets("Pending Debit").Sort


.SetRange Range("A23", Range("F23").End(xlDown))


.Header = xlGuess


.MatchCase = False


.Orientation = xlTopToBottom


.SortMethod = xlPinYin


.Apply


End With


ActiveWorkbook.Worksheets("Pending Debit").Select


ActiveWorkbook.Worksheets("Pending Debit").Range("B23").End(xlDown).Offset(1, 0).Select






End Sub

Display More

**royUK** · Apr 3rd 2020

Welcome to Ozgrid. Please take a few minutes to read the Forum Rules, there' a link in my signature below. I have added code tags this time.

An example workbook will make it easier to provide an answer.

jjconc · Apr 3rd 2020

Example worksheet is attached to original post.

**royUK** · Apr 3rd 2020

How does the sort work? Your data is not in a table format and will only sort Recurring Monthly Transactions

jjconc · Apr 3rd 2020

The sort macro I provided does work and is set-up to sort not the recurring, but 'Other Transactions'; I only want to sort the 'Other Transactions', I do not want to sort 'Recurring Transactions'; I do not understand reference to Table Format; I realize I only provided the portion of the macro for Pending Debit transactions;

**royUK** · Apr 3rd 2020

So what do you want to sort.

If you don't understand about working with data in Excel and table formats I suggest you start to learn That would be more important than VBA if this is the type of work you are doing.

jjconc · Apr 3rd 2020

I will learn about Tables

**royUK** · Apr 3rd 2020

Try this

Code

Option Explicit


Sub SortSheets1()
Dim oWs As Worksheet
Dim TargetCl As Range


For Each oWs In ThisWorkbook.Worksheets
With oWs
Select Case .Name
Case "Pending Debit", "Pending Credit"
Set TargetCl = .Cells(.Range("F2").Value, 2)
.Sort.SortFields.Clear
.Sort.SortFields.Add2 Key:=Range(TargetCl, TargetCl.End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange Range(TargetCl, TargetCl.End(xlDown))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Case Else
End Select
End With
Next oWs


End Sub

Display More

Use Variables for Range in Macro

Participate now!

Share

Tags