Macros produce inconsistent results across spreadsheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2303873%22%20slang%3D%22en-US%22%3EMacros%20produce%20inconsistent%20results%20across%20spreadsheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2303873%22%20slang%3D%22en-US%22%3E%3CUL%3E%3CLI%3E%3CSTRONG%3EDevice%20and%20OS%20platform%3C%2FSTRONG%3E%2C%20e.g.%2C%202020%20Macbook%20Air%2C%20running%20MacOs%2011.2.3%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EMicrosoft%20Excel%20for%20Mac%20version%2016.18%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3E%3CSTRONG%3E4%20relevant%20files%20Attached%3C%2FSTRONG%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSTRONG%3EHello%20All%2C%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20have%20one%20Master%20(Results)%20spreadsheet%20(attached)%2C%2015%20spreadsheets%20(games)%20which%20I%20had%20hoped%20to%20feed%20inputs%20into%20the%20master%20spreadsheet%20(more%20on%20that%20later)%2C%20and%201%20other%20workbook%20(schedule)%20which%20feeds%20data%20into%20the%2015%20(games).%20For%20the%20purposes%20of%20*hopefully*%20resolving%20this%20issue%2C%20I%20have%20attached%20the%20Master%2C%202%20of%20the%20games%20and%20the%20schedule.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAs%20yet%2C%20there%20are%20no%20issues%20feeding%20data%20from%20the%20schedule%20file%20to%20any%20of%20the%20games%20files.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20appears%20to%20occur%20when%20I%20run%20a%20Macro%20in%20the%20Results%20file%20that%20I%20had%20hoped%20would%20pull%20in%20all%20the%20data%20from%20the%20various%20games%20files.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20a%20perfect%20world%2C%20what%20this%20process%20would%20look%20like%20is%3A%3C%2FP%3E%3CP%3E1.)%20the%20Macro%2C%20located%20in%20the%20results%20file%2C%20and%20is%20called%20Picks_Calculate%20would%20copy%20data%20from%20the%20Data%20worksheet%20in%20the%20schedule%20file%20to%20the%20Adjusted%20Stats%20worksheet%20in%20each%20of%20the%20game%20files.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20Macro%20was%20broken%20up%20into%20four%20macros%20for%20each%20of%20the%20various%20game%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3E2.)%20The%20Macro%20would%20then%20copy%20the%20data%20from%20the%20results%20file%20(necessary%20for%20calculations%20in%20the%20games%20files)%20and%20then%20paste%20values%20that%20data%20to%20the%20various%20game%20files.%26nbsp%3B%3C%2FP%3E%3CP%3E3.)%20The%20Macro%20would%20then%3A%3C%2FP%3E%3CP%3E-Refresh%20the%20Adjusted%20Stats%20worksheet%3C%2FP%3E%3CP%3E-Refresh%20the%20Calculations%3C%2FP%3E%3CP%3E-Refresh%20the%20Simulations%20worksheet%20in%20the%20each%20of%20the%20games%20files.%3C%2FP%3E%3CP%3E-Run%20an%20advanced%20filter%20on%20the%20Weights%20worksheet%2C%20and%20copy%20and%20paste%20that%20data%20to%20the%20Weights1%20worksheet.%26nbsp%3B%3C%2FP%3E%3CP%3E-Refresh%20the%20Weight-Summary%20Statistics%20worksheet%3C%2FP%3E%3CP%3E4.)%20Close%20the%20schedule%20workbook%3C%2FP%3E%3CP%3E5.)%20Copy%20and%20paste%20some%20data%20as%20Paste%20Values%20from%20the%20Master%20and%20Simulations%20worksheets%20into%20the%20Picks-Generator%20worksheet%20in%20the%20Results%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E6.)%20The%20Macro%20then%20ought%20to%20run%20the%20macros%20responsible%20for%20the%20Macros%20responsible%20for%20those%20various%20tasks.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20a%20couple%20issues.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.)%20I%20don't%20get%20uniform%20results.%20In%20some%20instances%20the%20Macro%20works%20well%2C%20but%20in%20filtering%20in%20other%20data%20to%20the%20Results%20workbook%2C%20there%20is%20data%20missing.%26nbsp%3B%3C%2FP%3E%3CP%3E2.)%20When%20the%20Macro%20is%20run%2C%20in%20some%20of%20the%20Games%20workbooks%2C%20the%20formulae%20in%20the%20Master%20worksheet%20at%20J12%2C%2013%20are%20incorrectly%20applied%2C%20whereas%20in%20other%20Games%20workbooks%2C%20this%20is%20not%20an%20issue.%26nbsp%3B%3C%2FP%3E%3CP%3E3.)%20In%20some%20of%20the%20games%20workbooks%2C%20the%20advanced%20filter%20does%20not%20run%2C%20and%20subsequently%20the%20data%20is%20not%20pasted%20to%20the%20Weights1%20worksheet%20in%20the%20Games%20workbooks.%20I%20suspect%20it%20is%20likely%20this%20that%20is%20responsible%20for%20the%20issue%2C%20but%20how%20to%20fix%20it%3F%20Is%20there%20a%20way%20of%20accomplishing%20the%20same%20goal%20using%20solver%3F%26nbsp%3B%3C%2FP%3E%3CP%3E4.)%20some%20of%20the%20data%20that%20is%20pasted%20from%20the%20games%20workbooks%20to%20the%20Results%20workbook%20is%20incorrectly%20pasted.%20Specifically%2C%20I%20had%20a%20formula%20in%20the%20J2%20cell%20of%20the%20simulations%20worksheet%20in%20each%20of%20the%20games%20workbooks%20which%20was%20supposed%20to%20calculate%20the%20total%20of%20each%20row%20from%20B6%3AC10005%20and%20average%20them%20out.%20Instead%20it%20is%20showing%20incorrect%20data%20which%20is%20then%20pasted%20into%20the%20corresponding%20cell%20in%20the%20picks%20generator%20worksheet%20in%20the%20results%20workbook.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20apologize%20for%20the%20longwindedness%20of%20this%2C%20but%20I%20have%20been%20trying%20to%20get%20this%20up%20and%20running%20for%20a%20week%20now%2C%20and%20I%20have%20no%20idea%20how%20to%20fix%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20were%20to%20run%20the%20Macro%20without%20changing%20the%20data%20from%20Schedule%20or%20Results%20workbooks%2C%20you%20would%20see%20the%20issue.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20and%20all%20help%20would%20be%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2303873%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor
  • Device and OS platform, e.g., 2020 Macbook Air, running MacOs 11.2.3
  • Microsoft Excel for Mac version 16.18
  • 4 relevant files Attached

Hello All, 

 

I have one Master (Results) spreadsheet (attached), 15 spreadsheets (games) which I had hoped to feed inputs into the master spreadsheet (more on that later), and 1 other workbook (schedule) which feeds data into the 15 (games). For the purposes of *hopefully* resolving this issue, I have attached the Master, 2 of the games and the schedule. 

 

As yet, there are no issues feeding data from the schedule file to any of the games files. 

 

The issue appears to occur when I run a Macro in the Results file that I had hoped would pull in all the data from the various games files. 

 

In a perfect world, what this process would look like is:

1.) the Macro, located in the results file, and is called Picks_Calculate would copy data from the Data worksheet in the schedule file to the Adjusted Stats worksheet in each of the game files. 

This Macro was broken up into four macros for each of the various game sheets. 

2.) The Macro would then copy the data from the results file (necessary for calculations in the games files) and then paste values that data to the various game files. 

3.) The Macro would then:

-Refresh the Adjusted Stats worksheet

-Refresh the Calculations

-Refresh the Simulations worksheet in the each of the games files.

-Run an advanced filter on the Weights worksheet, and copy and paste that data to the Weights1 worksheet. 

-Refresh the Weight-Summary Statistics worksheet

4.) Close the schedule workbook

5.) Copy and paste some data as Paste Values from the Master and Simulations worksheets into the Picks-Generator worksheet in the Results file. 

6.) The Macro then ought to run the macros responsible for the Macros responsible for those various tasks. 

 

There are a couple issues. 

 

1.) I don't get uniform results. In some instances the Macro works well, but in filtering in other data to the Results workbook, there is data missing. 

2.) When the Macro is run, in some of the Games workbooks, the formulae in the Master worksheet at J12, 13 are incorrectly applied, whereas in other Games workbooks, this is not an issue. 

3.) In some of the games workbooks, the advanced filter does not run, and subsequently the data is not pasted to the Weights1 worksheet in the Games workbooks. I suspect it is likely this that is responsible for the issue, but how to fix it? Is there a way of accomplishing the same goal using solver? 

4.) some of the data that is pasted from the games workbooks to the Results workbook is incorrectly pasted. Specifically, I had a formula in the J2 cell of the simulations worksheet in each of the games workbooks which was supposed to calculate the total of each row from B6:C10005 and average them out. Instead it is showing incorrect data which is then pasted into the corresponding cell in the picks generator worksheet in the results workbook. 

 

I apologize for the longwindedness of this, but I have been trying to get this up and running for a week now, and I have no idea how to fix it. 

 

If you were to run the Macro without changing the data from Schedule or Results workbooks, you would see the issue. 

 

Any and all help would be greatly appreciated. 

 

Many thanks, 

Peter

 

 

0 Replies