How do I create a macro for excel that will work on all the sheets in the workbook?

Question

0.00/5 (No votes)

See more:

I have an Excel workbook that has 12 sheets, one for each month. I list all my monthly bills and enter the due dates for each bill. I would like to sort the bills by the due date and then by account name. I would like for this macro to work on whichever sheet I am on. Any suggestions?

What I have tried:

VB.NET

Sub Sort()

Keyboard Shortcut: Ctrl+s
    Range("A2:H28").Select
    ActiveWorkbook.Worksheets("JUN16").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JUN16").Sort.SortFields.Add Key:=Range("C2:C28"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("JUN16").Sort.SortFields.Add Key:=Range("A2:A28"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("JUN16").Sort
        .SetRange Range("A1:H28")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2").Select
End Sub

Posted 9-Jun-16 11:40am

Member 10376725

Updated 9-Jun-16 13:00pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Accepted Answer · 2016-06-09T13:00:00

Solution 1

You should try to replace all ActiveWorkbook.Worksheets("JUN16") with ActiveSheet.

Application.ActiveSheet Property (Excel)[^]

Posted 9-Jun-16 13:00pm

Patrice T

Comments

Member 10376725 13-Jun-16 15:52pm

ppolymorphe.. I tried your suggestion, first I got an "Expected end of statement error" with the "[^]" highlighted. got rid of the "[^]" and then got a "Sub or Function not defined" error. In trying to 'second guess' the problem I put a '.' between the ActiveSheet and Property and now get a "Type mismatch" error. I have re-recorded the macro and with your changes it looks like this:
' Keyboard Shortcut: Ctrl+s
'
Range("A2:H35").Select
ActiveSheet.Application.ActiveSheet Property(Excel)[^].Sort.SortFields.Clear
ActiveSheet.Application.ActiveSheet Property(Excel)[^].Sort.SortFields.Add Key:=Range("C2:C35"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Application.ActiveSheet Property(Excel)[^].Sort.SortFields.Add Key:=Range("A2:A35"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Application.ActiveSheet Property(Excel)[^].Sort
.SetRange Range("A1:H35")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub

What am I doing wrong?

Patrice T 13-Jun-16 17:53pm

look a&gain at solution.
second line is a link, not the same color as line 1
try
Range("A2:H35").Select
ActiveSheet.Sort.SortFields.Clear
...

Member 10376725 13-Jun-16 18:29pm

DUH... how embarrassing... Thanks ppolymorphe works perfect when ya do it right...