Macro Not Doing What I specified (Office 365)

S

SteveP29

New Member
Joined
Jul 1, 2007
Messages
34
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi, not sure if this problem has been addressed before, but I have a problem with a macro I've recorded for use in a spreadsheet.

I have a workbook saved on a USB drive.

Being a bit of a sports nut, I record the scores on a spreadsheet for the NFL.
I can't find a pre written template online that will calculate standings for me from just inputting scores.
I have the scores recorded and have built a data range that then calculates whether the game result is a win, loss or tie.
This then populates the record for each team.

To determine standings, that record then needs to be sorted.

I record a macro to copy the data, paste it where I want it and then sort it as per the League's rules.

After updating the scores each week, running the macro greys out while it is running, the rest of the tab that the data is being copied from, apart from the cells where the data will then be pasted.

When the macro has completed running, no data has been pasted and the cells are blank.

I've used macros like this for years and not being brilliant at VBA, I've always re-recorded the macro if I need to make tweaks and they've always worked.

Has this something to do with how Microsoft are now blocking macros from the internet, as in, the computer is treating the USB drive as an 'online' source or is there something else?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
If that were the problem, the macro wouldn't run at all, and you'd get a message to that effect.

Hard to fix the macro unless you post it though. ;)
 
Upvote 0
Apologies Rory, I should have thought about uploading my code, I did think that there might have been a simple yes/ no answer to the question so left it out.

I am running Office 2007 on another computer at home, I might also give the macro a try on there tonight.

What the macro is doing is copying each division (8 of them) and pasting them into the cells where they're to be sorted, I reckon I could probably cut down the procedure by copying and pasting all the data at the same time, then sorting the 8 divisions separately.
There are 2 copy ranges for each division because the calculation includes the number of games played, American sports don't include games played in their standings.

I hope this works, this is my VBA Code, if you're able to help, thanks in advance.

VBA Code: 
Sub Standings()
'
' Standings Macro
'

'
    Range("AO3:AO6,AQ3:AV6").Select
    Range("AQ3").Activate
    Selection.Copy
    Range("AX3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY3:AY6"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD3:BD6"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX3:BD6")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AO8:AO11,AQ8:AV11").Select
    Range("AQ8").Activate
    Selection.Copy
    Range("AX8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY8:AY11"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD8:BD11"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX8:BD11")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AO13:AO16,AQ13:AV16").Select
    Range("AQ13").Activate
    Selection.Copy
    Range("AX13").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY13:AY16"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD13:BD16"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX13:BD16")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AO18:AO21,AQ18:AV21").Select
    Range("AQ18").Activate
    Selection.Copy
    Range("AX18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY18:AY21"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD18:BD21"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX18:BD21")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AO23:AO26,AQ23:AV26").Select
    Range("AQ23").Activate
    Selection.Copy
    Range("AX23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY23:AY26"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD23:BD26"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX23:BD26")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AO28:AO31,AQ28:AV31").Select
    Range("AQ28").Activate
    Selection.Copy
    Range("AX28").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY28:AY31"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD28:BD31"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX28:BD31")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AO33:AO36,AQ33:AV36").Select
    Range("AQ33").Activate
    Selection.Copy
    Range("AX33").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY33:AY36"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD33:BD36"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX33:BD36")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AO38:AO41,AQ38:AV41").Select
    Range("AQ38").Activate
    Selection.Copy
    Range("AX38").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY38:AY41"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD38:BD41"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX38:BD41")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("T43").Select
End Sub
 
Upvote 0
There's a lot of repetition there, so I'd refactor the copying and sorting into a separate routine - something like:

Excel Formula: 
Sub Standings()
   With ActiveWorkbook.Worksheets("League Calculation")
      copyAndSort .Range("AO3:AO6,AQ3:AV6"), .Range("AX3")
      copyAndSort .Range("AO8:AO11,AQ8:AV11"), .Range("AX8")
      copyAndSort .Range("AO13:AO16,AQ13:AV16"), .Range("AX13")
      copyAndSort .Range("AO18:AO21,AQ18:AV21"), .Range("AX18")
      copyAndSort .Range("AO23:AO26,AQ23:AV26"), .Range("AX23")
      copyAndSort .Range("AO28:AO31,AQ28:AV31"), .Range("AX28")
      copyAndSort .Range("AO33:AO36,AQ33:AV36"), .Range("AX33")
      copyAndSort .Range("AO38:AO41,AQ38:AV41"), .Range("AX38")
   End With
End Sub

Sub copyAndSort(sourceRange As Range, destRange As Range)
   Dim dataSheet As Worksheet
   Set dataSheet = destRange.Worksheet
   Dim rowCount As Long
   rowCount = sourceRange.Areas(1).Rows.Count
   Dim colCount As Long
   Dim a As Range
   For Each a In sourceRange.Areas
      colCount = colCount + a.Columns.Count
   Next a
   
   sourceRange.Copy
   destRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                           SkipBlanks:=False, Transpose:=False
   With dataSheet.Sort
   
      With .SortFields
         .Clear
         .Add Key:=destRange.Cells(1, 2), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
         .Add Key:=destRange.Cells(1, colCount), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
      End With
      
      .SetRange destRange.Resize(rowCount, colCount)
      .Header = xlGuess
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With

End Sub

Since your ranges are equally spaced (and sized) you could streamline it further if you wanted.
 
Upvote 0
Solution
Some further info, the calculations are made on one tab (League Calculation) and the NFL tab has the standings formatted with colours etc and links via formula (ie =A7) to the sorted data.
I have assigned the macro to a 'button' I added from inserting a shape and entering text into it in the NFL tab.

Update:
So I ran the macro on the computer running Office 2007 and noticed that it was copying the cells in the same range but on the NFL tab (I didn't see that when attempting it on the Office 365 computer)
I moved the 'button' to the League Calculation tab and everything works perfectly now.
I've also updated and simplified the copy and paste macro, not as simplified as the example you gave yesterday, as I said, I'm not that confident working with VBA.

Thanks for your help! much appreciated.

VBA Code: 
Sub Standings()
'
' Standings Macro
'

'
    Range("AO3:AO41").Select
    Selection.Copy
    Range("AX3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AQ3:AV41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AY3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AX3:BD6").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY3:AY6"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD3:BD6"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX3:BD6")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AX8:BD11").Select
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY8:AY11"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD8:BD11"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX8:BD11")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AX13:BD16").Select
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY13:AY16"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD13:BD16"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX13:BD16")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AX18:BD21").Select
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY18:AY21"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD18:BD21"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX18:BD21")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AX23:BD26").Select
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY23:AY26"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD23:BD26"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX23:BD26")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AX28:BD31").Select
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY28:AY31"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD28:BD31"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX28:BD31")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AX33:BD36").Select
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY33:AY36"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD33:BD36"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX33:BD36")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AX38:BD41").Select
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("AY38:AY41"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("League Calculation").Sort.SortFields.Add2 Key:= _
        Range("BD38:BD41"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("League Calculation").Sort
        .SetRange Range("AX38:BD41")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A43").Select
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Excel Activate workbook1/sheet, Copy/paste row in workbook2/sheet, activate autofilter, return value and loop
Replies
0
Views
44
user29383902
U
D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
511
Fluff
Fluff
P
  • Question Question
Excel/365 VBA crash issues recently
Replies
0
Views
515
picklefactory
P
D
  • Question Question
Macro script and scheduler
Replies
1
Views
297
Micron
M
J
  • Solved
Macro runs more slowly each time it is run
2 3 4
Replies
34
Views
1K
Jehannum_2000
J

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top