Macro/VBA: Complete Newbie trying to Record a Macro

[James8761]

A complete newbie here trying to record a Macro to an arduous task. I collect information each month and then have to sort the Data by highest value. Starting in Column L I have a product name, then in Column M the sales for the month. Each month I go in highlight the data in Columns L and M and Sort by Column M, Highest value first.

This continues in exactly the same way over to Column BY. So I do the same thing in Column N/O, P/Q, R/S etc etc.

I tried to record a Macro. I’m using Excel 2016. I went to Developer/Code/Record Macro. Typed in a Macro Name. Left Store Macro in: This Workbook and pressed OK.

I then went and done my ‘Sorting’. Once I had finished I pressed Stop Recording in Developer/Code.

I then tried to Run the Macro to see if it worked by going to Developer/Code/Macros/Run and I get the following error message:
Microsoft Visual Basic for Applications
Compile error:
Invalid Outside Procedure

In the VBA Box above it says:
Worksheets(“Sheet1”).PrintOut From:=2, To:=3
End Sub

Does anyone know what I am doing wrong???

Thanks for any advice.

 

[James006]

Hi,

It would appear that the Worksheets(“Sheet1”) ... no longer exists ...

Would you have changed its name in between ???

Make sure to have the exact name in your code ...

Hope this will help

 

[James8761]

Hi,

It would appear that the Worksheets(“Sheet1”) ... no longer exists ...

Would you have changed its name in between ???

Make sure to have the exact name in your code ...

Hope this will help

Hi, thanks for the reply. I'm not sure. There isn't a Sheet1. The title of the sheet is called KPI and the data is all on the same sheet.

I'll go back to the old fashioned way and sort it manually each month!

Thanks for the response though.

 

[Logit]

.
I ran a simulation sort here using Cols A/B, C/D, E/F. Here is the result of the recorded macro :

Sub Sort_Two_Columns()
'
' Sort_Two_Columns Macro
'


'
    Range("A1:B20").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B20") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B20")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C1:D20").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D1:D20") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C1:D20")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("E1:F20").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F1:F20") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("E1:F20")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I'm not certain what you (or if you actually are) did wrong running the Record Macro. I find here sometimes Excel just "acts up" when it wants to. Frustrating to be certain. I believe Artificial Intelligence has already begun its fight against humanity.

I suppose worst case scenario, you could use the above example as a template and manually create your macro to cover the ranges required.

 

[James006]

Hi, thanks for the reply. I'm not sure. There isn't a Sheet1. The title of the sheet is called KPI and the data is all on the same sheet.

I'll go back to the old fashioned way and sort it manually each month!

Thanks for the response though.

Hi,

If you have the worksheet's tab name is KPI ... just type-in KPI instead of Sheet1 ... within your macro ...

Hope this will help