This is a three part blog:
Introduction,
Setting Up Your Own Excel Spreadsheet (this page), and
Using Your Spreadsheet.
The first Macro is called ImportPlayerData and is called by the aforementioned button press and looks like the following (comments in ExcelVBA (ie Excel macros) are preceded by an apostrophe like this ' ):
Sub ImportPlayerData()
'Paste the copied data into Section 3
Range("A80").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
'Delete the unrequired data eg Age, Resting, and Training Now columns.
Range("O80:P111").Select
Selection.Delete Shift:=xlToLeft
'Sort the data into positions and then alphabetical order
Range("B81:P111").Select
Selection.Sort Key1:=Range("B80"), Order1:=xlAscending, Key2:=Range("C80" _
), Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
' Remove the Q increase from the right of the Q value
Dim c As Variant
Dim d As Variant
Dim remover As Variant
For Each c In Range("D81:D110")
d = c.Value
remover = Val(d)
c.Value = remover
Next
'Remove the "Inj" from the fitness of injured players (not required when copying from Google Chrome)
Dim e As Variant
Dim f As Variant
Dim remover2 As Variant
For Each e In Range("M81:M110")
f = e.Value
remover = Val(f)
e.Value = remover
Next
'Start the macro called CalcChanges
Call CalcChanges
'Copy the data from Section 3 to Section 2
Range("B81:P111").Select
Selection.Copy
Range("B37").Select
ActiveSheet.Paste
End Sub
The CalcChanges Macro that is called from the above macro is excessively long as each section is for each player and each line subtracts the old attribute from the current attribute and then puts the value in Section 5. It looks like this:
Sub CalcChanges()
'This subtracts the player's previously pasted stats from their current stats.
'1
Cells(37, 21).Value = Cells(81, 4).Value - Cells(37, 4).Value
Cells(37, 22).Value = Cells(81, 5).Value - Cells(37, 5).Value
Cells(37, 23).Value = Cells(81, 6).Value - Cells(37, 6).Value
Cells(37, 24).Value = Cells(81, 7).Value - Cells(37, 7).Value
Cells(37, 25).Value = Cells(81, 8).Value - Cells(37, 8).Value
Cells(37, 26).Value = Cells(81, 9).Value - Cells(37, 9).Value
Cells(37, 27).Value = Cells(81, 10).Value - Cells(37, 10).Value
Cells(37, 28).Value = Cells(81, 11).Value - Cells(37, 11).Value
Cells(37, 29).Value = Cells(81, 12).Value - Cells(37, 12).Value
Cells(37, 30).Value = Cells(81, 13).Value - Cells(37, 13).Value
'2
Cells(38, 21).Value = Cells(82, 4).Value - Cells(38, 4).Value
Cells(38, 22).Value = Cells(82, 5).Value - Cells(38, 5).Value
Cells(38, 23).Value = Cells(82, 6).Value - Cells(38, 6).Value
Cells(38, 24).Value = Cells(82, 7).Value - Cells(38, 7).Value
Cells(38, 25).Value = Cells(82, 8).Value - Cells(38, 8).Value
Cells(38, 26).Value = Cells(82, 9).Value - Cells(38, 9).Value
Cells(38, 27).Value = Cells(82, 10).Value - Cells(38, 10).Value
Cells(38, 28).Value = Cells(82, 11).Value - Cells(38, 11).Value
Cells(38, 29).Value = Cells(82, 12).Value - Cells(38, 12).Value
Cells(38, 30).Value = Cells(82, 13).Value - Cells(38, 13).Value
'3
Cells(39, 21).Value = Cells(83, 4).Value - Cells(39, 4).Value
Cells(39, 22).Value = Cells(83, 5).Value - Cells(39, 5).Value
Cells(39, 23).Value = Cells(83, 6).Value - Cells(39, 6).Value
Cells(39, 24).Value = Cells(83, 7).Value - Cells(39, 7).Value
Cells(39, 25).Value = Cells(83, 8).Value - Cells(39, 8).Value
Cells(39, 26).Value = Cells(83, 9).Value - Cells(39, 9).Value
Cells(39, 27).Value = Cells(83, 10).Value - Cells(39, 10).Value
Cells(39, 28).Value = Cells(83, 11).Value - Cells(39, 11).Value
Cells(39, 29).Value = Cells(83, 12).Value - Cells(39, 12).Value
Cells(39, 30).Value = Cells(83, 13).Value - Cells(39, 13).Value
'4
Cells(40, 21).Value = Cells(84, 4).Value - Cells(40, 4).Value
Cells(40, 22).Value = Cells(84, 5).Value - Cells(40, 5).Value
Cells(40, 23).Value = Cells(84, 6).Value - Cells(40, 6).Value
Cells(40, 24).Value = Cells(84, 7).Value - Cells(40, 7).Value
Cells(40, 25).Value = Cells(84, 8).Value - Cells(40, 8).Value
Cells(40, 26).Value = Cells(84, 9).Value - Cells(40, 9).Value
Cells(40, 27).Value = Cells(84, 10).Value - Cells(40, 10).Value
Cells(40, 28).Value = Cells(84, 11).Value - Cells(40, 11).Value
Cells(40, 29).Value = Cells(84, 12).Value - Cells(40, 12).Value
Cells(40, 30).Value = Cells(84, 13).Value - Cells(40, 13).Value
'5
Cells(41, 21).Value = Cells(85, 4).Value - Cells(41, 4).Value
Cells(41, 22).Value = Cells(85, 5).Value - Cells(41, 5).Value
Cells(41, 23).Value = Cells(85, 6).Value - Cells(41, 6).Value
Cells(41, 24).Value = Cells(85, 7).Value - Cells(41, 7).Value
Cells(41, 25).Value = Cells(85, 8).Value - Cells(41, 8).Value
Cells(41, 26).Value = Cells(85, 9).Value - Cells(41, 9).Value
Cells(41, 27).Value = Cells(85, 10).Value - Cells(41, 10).Value
Cells(41, 28).Value = Cells(85, 11).Value - Cells(41, 11).Value
Cells(41, 29).Value = Cells(85, 12).Value - Cells(41, 12).Value
Cells(41, 30).Value = Cells(85, 13).Value - Cells(41, 13).Value
'6
Cells(42, 21).Value = Cells(86, 4).Value - Cells(42, 4).Value
Cells(42, 22).Value = Cells(86, 5).Value - Cells(42, 5).Value
Cells(42, 23).Value = Cells(86, 6).Value - Cells(42, 6).Value
Cells(42, 24).Value = Cells(86, 7).Value - Cells(42, 7).Value
Cells(42, 25).Value = Cells(86, 8).Value - Cells(42, 8).Value
Cells(42, 26).Value = Cells(86, 9).Value - Cells(42, 9).Value
Cells(42, 27).Value = Cells(86, 10).Value - Cells(42, 10).Value
Cells(42, 28).Value = Cells(86, 11).Value - Cells(42, 11).Value
Cells(42, 29).Value = Cells(86, 12).Value - Cells(42, 12).Value
Cells(42, 30).Value = Cells(86, 13).Value - Cells(42, 13).Value
'7
Cells(43, 21).Value = Cells(87, 4).Value - Cells(43, 4).Value
Cells(43, 22).Value = Cells(87, 5).Value - Cells(43, 5).Value
Cells(43, 23).Value = Cells(87, 6).Value - Cells(43, 6).Value
Cells(43, 24).Value = Cells(87, 7).Value - Cells(43, 7).Value
Cells(43, 25).Value = Cells(87, 8).Value - Cells(43, 8).Value
Cells(43, 26).Value = Cells(87, 9).Value - Cells(43, 9).Value
Cells(43, 27).Value = Cells(87, 10).Value - Cells(43, 10).Value
Cells(43, 28).Value = Cells(87, 11).Value - Cells(43, 11).Value
Cells(43, 29).Value = Cells(87, 12).Value - Cells(43, 12).Value
Cells(43, 30).Value = Cells(87, 13).Value - Cells(43, 13).Value
'8
Cells(44, 21).Value = Cells(88, 4).Value - Cells(44, 4).Value
Cells(44, 22).Value = Cells(88, 5).Value - Cells(44, 5).Value
Cells(44, 23).Value = Cells(88, 6).Value - Cells(44, 6).Value
Cells(44, 24).Value = Cells(88, 7).Value - Cells(44, 7).Value
Cells(44, 25).Value = Cells(88, 8).Value - Cells(44, 8).Value
Cells(44, 26).Value = Cells(88, 9).Value - Cells(44, 9).Value
Cells(44, 27).Value = Cells(88, 10).Value - Cells(44, 10).Value
Cells(44, 28).Value = Cells(88, 11).Value - Cells(44, 11).Value
Cells(44, 29).Value = Cells(88, 12).Value - Cells(44, 12).Value
Cells(44, 30).Value = Cells(88, 13).Value - Cells(44, 13).Value
'9
Cells(45, 21).Value = Cells(89, 4).Value - Cells(45, 4).Value
Cells(45, 22).Value = Cells(89, 5).Value - Cells(45, 5).Value
Cells(45, 23).Value = Cells(89, 6).Value - Cells(45, 6).Value
Cells(45, 24).Value = Cells(89, 7).Value - Cells(45, 7).Value
Cells(45, 25).Value = Cells(89, 8).Value - Cells(45, 8).Value
Cells(45, 26).Value = Cells(89, 9).Value - Cells(45, 9).Value
Cells(45, 27).Value = Cells(89, 10).Value - Cells(45, 10).Value
Cells(45, 28).Value = Cells(89, 11).Value - Cells(45, 11).Value
Cells(45, 29).Value = Cells(89, 12).Value - Cells(45, 12).Value
Cells(45, 30).Value = Cells(89, 13).Value - Cells(45, 13).Value
'10
Cells(46, 21).Value = Cells(90, 4).Value - Cells(46, 4).Value
Cells(46, 22).Value = Cells(90, 5).Value - Cells(46, 5).Value
Cells(46, 23).Value = Cells(90, 6).Value - Cells(46, 6).Value
Cells(46, 24).Value = Cells(90, 7).Value - Cells(46, 7).Value
Cells(46, 25).Value = Cells(90, 8).Value - Cells(46, 8).Value
Cells(46, 26).Value = Cells(90, 9).Value - Cells(46, 9).Value
Cells(46, 27).Value = Cells(90, 10).Value - Cells(46, 10).Value
Cells(46, 28).Value = Cells(90, 11).Value - Cells(46, 11).Value
Cells(46, 29).Value = Cells(90, 12).Value - Cells(46, 12).Value
Cells(46, 30).Value = Cells(90, 13).Value - Cells(46, 13).Value
'11
Cells(47, 21).Value = Cells(91, 4).Value - Cells(47, 4).Value
Cells(47, 22).Value = Cells(91, 5).Value - Cells(47, 5).Value
Cells(47, 23).Value = Cells(91, 6).Value - Cells(47, 6).Value
Cells(47, 24).Value = Cells(91, 7).Value - Cells(47, 7).Value
Cells(47, 25).Value = Cells(91, 8).Value - Cells(47, 8).Value
Cells(47, 26).Value = Cells(91, 9).Value - Cells(47, 9).Value
Cells(47, 27).Value = Cells(91, 10).Value - Cells(47, 10).Value
Cells(47, 28).Value = Cells(91, 11).Value - Cells(47, 11).Value
Cells(47, 29).Value = Cells(91, 12).Value - Cells(47, 12).Value
Cells(47, 30).Value = Cells(91, 13).Value - Cells(47, 13).Value
'12
Cells(48, 21).Value = Cells(92, 4).Value - Cells(48, 4).Value
Cells(48, 22).Value = Cells(92, 5).Value - Cells(48, 5).Value
Cells(48, 23).Value = Cells(92, 6).Value - Cells(48, 6).Value
Cells(48, 24).Value = Cells(92, 7).Value - Cells(48, 7).Value
Cells(48, 25).Value = Cells(92, 8).Value - Cells(48, 8).Value
Cells(48, 26).Value = Cells(92, 9).Value - Cells(48, 9).Value
Cells(48, 27).Value = Cells(92, 10).Value - Cells(48, 10).Value
Cells(48, 28).Value = Cells(92, 11).Value - Cells(48, 11).Value
Cells(48, 29).Value = Cells(92, 12).Value - Cells(48, 12).Value
Cells(48, 30).Value = Cells(92, 13).Value - Cells(48, 13).Value
'13
Cells(49, 21).Value = Cells(93, 4).Value - Cells(49, 4).Value
Cells(49, 22).Value = Cells(93, 5).Value - Cells(49, 5).Value
Cells(49, 23).Value = Cells(93, 6).Value - Cells(49, 6).Value
Cells(49, 24).Value = Cells(93, 7).Value - Cells(49, 7).Value
Cells(49, 25).Value = Cells(93, 8).Value - Cells(49, 8).Value
Cells(49, 26).Value = Cells(93, 9).Value - Cells(49, 9).Value
Cells(49, 27).Value = Cells(93, 10).Value - Cells(49, 10).Value
Cells(49, 28).Value = Cells(93, 11).Value - Cells(49, 11).Value
Cells(49, 29).Value = Cells(93, 12).Value - Cells(49, 12).Value
Cells(49, 30).Value = Cells(93, 13).Value - Cells(49, 13).Value
'14
Cells(50, 21).Value = Cells(94, 4).Value - Cells(50, 4).Value
Cells(50, 22).Value = Cells(94, 5).Value - Cells(50, 5).Value
Cells(50, 23).Value = Cells(94, 6).Value - Cells(50, 6).Value
Cells(50, 24).Value = Cells(94, 7).Value - Cells(50, 7).Value
Cells(50, 25).Value = Cells(94, 8).Value - Cells(50, 8).Value
Cells(50, 26).Value = Cells(94, 9).Value - Cells(50, 9).Value
Cells(50, 27).Value = Cells(94, 10).Value - Cells(50, 10).Value
Cells(50, 28).Value = Cells(94, 11).Value - Cells(50, 11).Value
Cells(50, 29).Value = Cells(94, 12).Value - Cells(50, 12).Value
Cells(50, 30).Value = Cells(94, 13).Value - Cells(50, 13).Value
'15
Cells(51, 21).Value = Cells(95, 4).Value - Cells(51, 4).Value
Cells(51, 22).Value = Cells(95, 5).Value - Cells(51, 5).Value
Cells(51, 23).Value = Cells(95, 6).Value - Cells(51, 6).Value
Cells(51, 24).Value = Cells(95, 7).Value - Cells(51, 7).Value
Cells(51, 25).Value = Cells(95, 8).Value - Cells(51, 8).Value
Cells(51, 26).Value = Cells(95, 9).Value - Cells(51, 9).Value
Cells(51, 27).Value = Cells(95, 10).Value - Cells(51, 10).Value
Cells(51, 28).Value = Cells(95, 11).Value - Cells(51, 11).Value
Cells(51, 29).Value = Cells(95, 12).Value - Cells(51, 12).Value
Cells(51, 30).Value = Cells(95, 13).Value - Cells(51, 13).Value
'16
Cells(52, 21).Value = Cells(96, 4).Value - Cells(52, 4).Value
Cells(52, 22).Value = Cells(96, 5).Value - Cells(52, 5).Value
Cells(52, 23).Value = Cells(96, 6).Value - Cells(52, 6).Value
Cells(52, 24).Value = Cells(96, 7).Value - Cells(52, 7).Value
Cells(52, 25).Value = Cells(96, 8).Value - Cells(52, 8).Value
Cells(52, 26).Value = Cells(96, 9).Value - Cells(52, 9).Value
Cells(52, 27).Value = Cells(96, 10).Value - Cells(52, 10).Value
Cells(52, 28).Value = Cells(96, 11).Value - Cells(52, 11).Value
Cells(52, 29).Value = Cells(96, 12).Value - Cells(52, 12).Value
Cells(52, 30).Value = Cells(96, 13).Value - Cells(52, 13).Value
'17
Cells(53, 21).Value = Cells(97, 4).Value - Cells(53, 4).Value
Cells(53, 22).Value = Cells(97, 5).Value - Cells(53, 5).Value
Cells(53, 23).Value = Cells(97, 6).Value - Cells(53, 6).Value
Cells(53, 24).Value = Cells(97, 7).Value - Cells(53, 7).Value
Cells(53, 25).Value = Cells(97, 8).Value - Cells(53, 8).Value
Cells(53, 26).Value = Cells(97, 9).Value - Cells(53, 9).Value
Cells(53, 27).Value = Cells(97, 10).Value - Cells(53, 10).Value
Cells(53, 28).Value = Cells(97, 11).Value - Cells(53, 11).Value
Cells(53, 29).Value = Cells(97, 12).Value - Cells(53, 12).Value
Cells(53, 30).Value = Cells(97, 13).Value - Cells(53, 13).Value
'18
Cells(54, 21).Value = Cells(98, 4).Value - Cells(54, 4).Value
Cells(54, 22).Value = Cells(98, 5).Value - Cells(54, 5).Value
Cells(54, 23).Value = Cells(98, 6).Value - Cells(54, 6).Value
Cells(54, 24).Value = Cells(98, 7).Value - Cells(54, 7).Value
Cells(54, 25).Value = Cells(98, 8).Value - Cells(54, 8).Value
Cells(54, 26).Value = Cells(98, 9).Value - Cells(54, 9).Value
Cells(54, 27).Value = Cells(98, 10).Value - Cells(54, 10).Value
Cells(54, 28).Value = Cells(98, 11).Value - Cells(54, 11).Value
Cells(54, 29).Value = Cells(98, 12).Value - Cells(54, 12).Value
Cells(54, 30).Value = Cells(98, 13).Value - Cells(54, 13).Value
'19
Cells(55, 21).Value = Cells(99, 4).Value - Cells(55, 4).Value
Cells(55, 22).Value = Cells(99, 5).Value - Cells(55, 5).Value
Cells(55, 23).Value = Cells(99, 6).Value - Cells(55, 6).Value
Cells(55, 24).Value = Cells(99, 7).Value - Cells(55, 7).Value
Cells(55, 25).Value = Cells(99, 8).Value - Cells(55, 8).Value
Cells(55, 26).Value = Cells(99, 9).Value - Cells(55, 9).Value
Cells(55, 27).Value = Cells(99, 10).Value - Cells(55, 10).Value
Cells(55, 28).Value = Cells(99, 11).Value - Cells(55, 11).Value
Cells(55, 29).Value = Cells(99, 12).Value - Cells(55, 12).Value
Cells(55, 30).Value = Cells(99, 13).Value - Cells(55, 13).Value
'20
Cells(56, 21).Value = Cells(100, 4).Value - Cells(56, 4).Value
Cells(56, 22).Value = Cells(100, 5).Value - Cells(56, 5).Value
Cells(56, 23).Value = Cells(100, 6).Value - Cells(56, 6).Value
Cells(56, 24).Value = Cells(100, 7).Value - Cells(56, 7).Value
Cells(56, 25).Value = Cells(100, 8).Value - Cells(56, 8).Value
Cells(56, 26).Value = Cells(100, 9).Value - Cells(56, 9).Value
Cells(56, 27).Value = Cells(100, 10).Value - Cells(56, 10).Value
Cells(56, 28).Value = Cells(100, 11).Value - Cells(56, 11).Value
Cells(56, 29).Value = Cells(100, 12).Value - Cells(56, 12).Value
Cells(56, 30).Value = Cells(100, 13).Value - Cells(56, 13).Value
'21
Cells(57, 21).Value = Cells(101, 4).Value - Cells(57, 4).Value
Cells(57, 22).Value = Cells(101, 5).Value - Cells(57, 5).Value
Cells(57, 23).Value = Cells(101, 6).Value - Cells(57, 6).Value
Cells(57, 24).Value = Cells(101, 7).Value - Cells(57, 7).Value
Cells(57, 25).Value = Cells(101, 8).Value - Cells(57, 8).Value
Cells(57, 26).Value = Cells(101, 9).Value - Cells(57, 9).Value
Cells(57, 27).Value = Cells(101, 10).Value - Cells(57, 10).Value
Cells(57, 28).Value = Cells(101, 11).Value - Cells(57, 11).Value
Cells(57, 29).Value = Cells(101, 12).Value - Cells(57, 12).Value
Cells(57, 30).Value = Cells(101, 13).Value - Cells(57, 13).Value
'22
Cells(58, 21).Value = Cells(102, 4).Value - Cells(58, 4).Value
Cells(58, 22).Value = Cells(102, 5).Value - Cells(58, 5).Value
Cells(58, 23).Value = Cells(102, 6).Value - Cells(58, 6).Value
Cells(58, 24).Value = Cells(102, 7).Value - Cells(58, 7).Value
Cells(58, 25).Value = Cells(102, 8).Value - Cells(58, 8).Value
Cells(58, 26).Value = Cells(102, 9).Value - Cells(58, 9).Value
Cells(58, 27).Value = Cells(102, 10).Value - Cells(58, 10).Value
Cells(58, 28).Value = Cells(102, 11).Value - Cells(58, 11).Value
Cells(58, 29).Value = Cells(102, 12).Value - Cells(58, 12).Value
Cells(58, 30).Value = Cells(102, 13).Value - Cells(58, 13).Value
'23
Cells(59, 21).Value = Cells(103, 4).Value - Cells(59, 4).Value
Cells(59, 22).Value = Cells(103, 5).Value - Cells(59, 5).Value
Cells(59, 23).Value = Cells(103, 6).Value - Cells(59, 6).Value
Cells(59, 24).Value = Cells(103, 7).Value - Cells(59, 7).Value
Cells(59, 25).Value = Cells(103, 8).Value - Cells(59, 8).Value
Cells(59, 26).Value = Cells(103, 9).Value - Cells(59, 9).Value
Cells(59, 27).Value = Cells(103, 10).Value - Cells(59, 10).Value
Cells(59, 28).Value = Cells(103, 11).Value - Cells(59, 11).Value
Cells(59, 29).Value = Cells(103, 12).Value - Cells(59, 12).Value
Cells(59, 30).Value = Cells(103, 13).Value - Cells(59, 13).Value
'24
Cells(60, 21).Value = Cells(104, 4).Value - Cells(60, 4).Value
Cells(60, 22).Value = Cells(104, 5).Value - Cells(60, 5).Value
Cells(60, 23).Value = Cells(104, 6).Value - Cells(60, 6).Value
Cells(60, 24).Value = Cells(104, 7).Value - Cells(60, 7).Value
Cells(60, 25).Value = Cells(104, 8).Value - Cells(60, 8).Value
Cells(60, 26).Value = Cells(104, 9).Value - Cells(60, 9).Value
Cells(60, 27).Value = Cells(104, 10).Value - Cells(60, 10).Value
Cells(60, 28).Value = Cells(104, 11).Value - Cells(60, 11).Value
Cells(60, 29).Value = Cells(104, 12).Value - Cells(60, 12).Value
Cells(60, 30).Value = Cells(104, 13).Value - Cells(60, 13).Value
'25
Cells(61, 21).Value = Cells(105, 4).Value - Cells(61, 4).Value
Cells(61, 22).Value = Cells(105, 5).Value - Cells(61, 5).Value
Cells(61, 23).Value = Cells(105, 6).Value - Cells(61, 6).Value
Cells(61, 24).Value = Cells(105, 7).Value - Cells(61, 7).Value
Cells(61, 25).Value = Cells(105, 8).Value - Cells(61, 8).Value
Cells(61, 26).Value = Cells(105, 9).Value - Cells(61, 9).Value
Cells(61, 27).Value = Cells(105, 10).Value - Cells(61, 10).Value
Cells(61, 28).Value = Cells(105, 11).Value - Cells(61, 11).Value
Cells(61, 29).Value = Cells(105, 12).Value - Cells(61, 12).Value
Cells(61, 30).Value = Cells(105, 13).Value - Cells(61, 13).Value
'26
Cells(62, 21).Value = Cells(106, 4).Value - Cells(62, 4).Value
Cells(62, 22).Value = Cells(106, 5).Value - Cells(62, 5).Value
Cells(62, 23).Value = Cells(106, 6).Value - Cells(62, 6).Value
Cells(62, 24).Value = Cells(106, 7).Value - Cells(62, 7).Value
Cells(62, 25).Value = Cells(106, 8).Value - Cells(62, 8).Value
Cells(62, 26).Value = Cells(106, 9).Value - Cells(62, 9).Value
Cells(62, 27).Value = Cells(106, 10).Value - Cells(62, 10).Value
Cells(62, 28).Value = Cells(106, 11).Value - Cells(62, 11).Value
Cells(62, 29).Value = Cells(106, 12).Value - Cells(62, 12).Value
Cells(62, 30).Value = Cells(106, 13).Value - Cells(62, 13).Value
'27
Cells(63, 21).Value = Cells(107, 4).Value - Cells(63, 4).Value
Cells(63, 22).Value = Cells(107, 5).Value - Cells(63, 5).Value
Cells(63, 23).Value = Cells(107, 6).Value - Cells(63, 6).Value
Cells(63, 24).Value = Cells(107, 7).Value - Cells(63, 7).Value
Cells(63, 25).Value = Cells(107, 8).Value - Cells(63, 8).Value
Cells(63, 26).Value = Cells(107, 9).Value - Cells(63, 9).Value
Cells(63, 27).Value = Cells(107, 10).Value - Cells(63, 10).Value
Cells(63, 28).Value = Cells(107, 11).Value - Cells(63, 11).Value
Cells(63, 29).Value = Cells(107, 12).Value - Cells(63, 12).Value
Cells(63, 30).Value = Cells(107, 13).Value - Cells(63, 13).Value
'28
Cells(64, 21).Value = Cells(108, 4).Value - Cells(64, 4).Value
Cells(64, 22).Value = Cells(108, 5).Value - Cells(64, 5).Value
Cells(64, 23).Value = Cells(108, 6).Value - Cells(64, 6).Value
Cells(64, 24).Value = Cells(108, 7).Value - Cells(64, 7).Value
Cells(64, 25).Value = Cells(108, 8).Value - Cells(64, 8).Value
Cells(64, 26).Value = Cells(108, 9).Value - Cells(64, 9).Value
Cells(64, 27).Value = Cells(108, 10).Value - Cells(64, 10).Value
Cells(64, 28).Value = Cells(108, 11).Value - Cells(64, 11).Value
Cells(64, 29).Value = Cells(108, 12).Value - Cells(64, 12).Value
Cells(64, 30).Value = Cells(108, 13).Value - Cells(64, 13).Value
'29
Cells(65, 21).Value = Cells(109, 4).Value - Cells(65, 4).Value
Cells(65, 22).Value = Cells(109, 5).Value - Cells(65, 5).Value
Cells(65, 23).Value = Cells(109, 6).Value - Cells(65, 6).Value
Cells(65, 24).Value = Cells(109, 7).Value - Cells(65, 7).Value
Cells(65, 25).Value = Cells(109, 8).Value - Cells(65, 8).Value
Cells(65, 26).Value = Cells(109, 9).Value - Cells(65, 9).Value
Cells(65, 27).Value = Cells(109, 10).Value - Cells(65, 10).Value
Cells(65, 28).Value = Cells(109, 11).Value - Cells(65, 11).Value
Cells(65, 29).Value = Cells(109, 12).Value - Cells(65, 12).Value
Cells(65, 30).Value = Cells(109, 13).Value - Cells(65, 13).Value
'30
Cells(66, 21).Value = Cells(110, 4).Value - Cells(66, 4).Value
Cells(66, 22).Value = Cells(110, 5).Value - Cells(66, 5).Value
Cells(66, 23).Value = Cells(110, 6).Value - Cells(66, 6).Value
Cells(66, 24).Value = Cells(110, 7).Value - Cells(66, 7).Value
Cells(66, 25).Value = Cells(110, 8).Value - Cells(66, 8).Value
Cells(66, 26).Value = Cells(110, 9).Value - Cells(66, 9).Value
Cells(66, 27).Value = Cells(110, 10).Value - Cells(66, 10).Value
Cells(66, 28).Value = Cells(110, 11).Value - Cells(66, 11).Value
Cells(66, 29).Value = Cells(110, 12).Value - Cells(66, 12).Value
Cells(66, 30).Value = Cells(110, 13).Value - Cells(66, 13).Value
End Sub
As you can see from the last macro, this spreadsheet will only currently work with 30 players but you can change this with some knowledgeable manipulation ie it requires changing the formulae in the spreadsheet and some of the macros cell addressing.
Setting Up the Spreadsheet.
The easiest way to set this spreadsheet up is to go into Excel and select Tools -> Macros -> Visual Basic Editor (or press Alt+F11) and then go to Insert -> Module. An empty sheet will appear and this is where you paste the macros from above into them. Copy the first macro above from "Sub ImportPlayerData() ..." to "... End Sub" and go back to the Visual Basic Editor and paste it into the module. Repeat this for the second macro above (including the Sub and End Sub lines) and then save the Excel workbook with whatever name you choose - I use Attributes Season 44 etc.
Come back to ML and go to Team -> Individual Training. Drag your mouse to select from just before "Pos" at the top to just beyond the "Total" value of the bottom player listed. The selection should look like the following:
Now hit Ctrl+c (or right click the mouse and select Copy) to copy the selected data and go back to your Excel spreadsheet. Hit Alt+F8 (or Tools -> Macro -> Macros...) and select the ImportPlayerData macro from the list of macros and select Run. This will paste the data that you copied from ML into the worksheet and setup the basic positions of Sections 2, 3 and 5. Select from Cell A80 to Cell N110 (ie the whole of Section 3) and hit Ctrl+c or use the mouse to select Copy. Now select Cell A2 and paste, select Cell R2 and paste, select cell A36 and paste, and select cell R36 and paste. Now all 5 sections have the same data in them. Click on the A at the top of Column A and it should select the whole column. Drag with the mouse button depressed to select all of the columns up to AG and double click on the line in between any of the two columns selected. This should reformat the column widths to adjust for the data contained in them ie name columns are wider and attribute columns are narrower.
Now you start adding the formulae in cells. Start with the most complex in Section 4 (ie R2:AG32). In cell U3 enter the formula " =D37-D3 " (without the double apostrophes). The result of the formula should be zero as the data in Section 1 and 2 are the same. At the bottom right of the cell is a little square, click and drag this down to cell U32 to copy the formula down the column. Click the little square again and drag to the right to column AC to copy the formulae up to the Perception column. In cell AD2 type "Total", in AE2 type "Main" to signify main stats and then in cell AF2 Type "% MS" to signify the percentage of attribute gains that are main attributes. Delete the data in column AG as it is unused.
The next task is to colour the main stats of each position in Section 4 - so select the cells that refer to the Sh, He and Sp of the Attackers and click the "paint tin" and select red to put the backgrounds of the cells to red and then do the PE cells. Do this to all of the appropriate cells, using green background and yellow text for defenders (yellow text appears better against the dark background) (Tk, Pa, Sp, St), blue background and yellow text for goalkeepers (Ke, Pa, Sp , Pe) and yellow background for midfielders (Tk, Pa, Sh, Sp).
In Cell AD3 type the formula " =Sum(V3:AC3) " and then drag this formula down to cell AD32 using the little square again. In cell AE3 type "=" and then select the shooting cell of the first attacker (ie cell Y3) and then type "+" and then click on the heading attribute cell, then "+", then the speed attribute cell, then "+", and finally click on the perception attribute cell and hit enter. Use the little square again to copy this formula to the AE column of the other attackers. Use the same process of adding up the main stats of the first defender and use the square to drag it down the column for all Defs and do the same for the GKs and Mids. In cell AF3 type the formula " =AE3/AD3 " to get the percentage of main stats gained compared to all attributes. The result will be "#DIV/0!" which means you cannot divide by zero. When some gains are shown in the spreadsheet this formula should work properly. Drag this formula down to cell A33 using the little square to copy it down the column.
In Cell U33 type the formula " =SUM(U3:U32) " and this will total the amount of Q gains shown in the spreadsheet. Drag this using the little square on the cell to the right up to column AE. Select cell U33 to cell AF33 and put a border around it to signify it is separate to the individual attributes above - I prefer to use the single line on top and double line on the bottom of the cell. The final cosmetic touch is to select all of the attribute and formula cells in Section 4 and put the alignment to center.
That is Section 4, the most difficult part completed. Go to Section 2 and in Cell C67 enter the formula " =SUM(D37:D66)/A65 " where A65 is the last player in the list of 29 players ... if you have 25 players then it will be A61 etc.
Go to Section 5 and select from cell AE36 to AG66 and delete the data in this range. Go back to cell AE36 and type "Total". In cell AE37 type the formula " =SUM(V37:AC37) " (ie from Kp to Pe). Drag this formula to cell AE66 using the little square. In cell U67 type the formula " =SUM(U37:U66) " and drag this cell (using the little square once again) to cell AE67. Put the border on the bottom formulae similar to what was done in Section 4.
Now the last thing is to make a button to start the ImportPlayerData macro in an easier fashion. Go to View -> Toolbars -> Control Toolbox. Select the button that says "Command Button" and is the sixth button listed. Click and drag the button somewhere on the spreadsheet - mine is in between Section 2 and 3. Once you get a good sized button that does not cover any speadsheet data, double-click on this button to bring up the Visual Basic Editor again with the start code already there like this:
Private Sub CommandButton1_Click()
End Sub
Click in between the lines and add this code - " Call ImportPlayerData " - so it will end up looking like this:
Private Sub CommandButton1_Click()
Call ImportPlayerData
End Sub
Go to File -> Close and Return to Microsoft Excel
You can improve the look of the button by right-clicking on the button and selecting Properties. Change the Caption line to "Import New Data" or something similar and you can change the font, colour, size etc. Once happy, make sure you click the first button of the Control Toolbar ie "Exit Design Mode" otherwise Excel will become unresponsive and not do what you want it to with the mouse :) Close the Control Toolbar.
Now you have a fully functioning Spreadsheet
This may look like it is complicated but if you are capable in Excel then it should only take 15 - 30 minutes.
Go to the third and final part of this blog -
Using Your Spreadsheet.