Autosum formula within a macro

K

kazfowler

Joined
Mar 31, 2011
Messages
1
Reaction score
0
Hi,

I am trying to create a macro from one worksheet which autosums a variable column within another worksheet in the same workbook. I have to put on a few filters etc before performing the autosum function, however when performing the autosum function on a column it places the sum at the end of the worksheet. At the moment I have just copied and pasted that answer in to the designated cell in the other worksheet however depending on the data I download, this autosum may not always be in that cell. Please can you advise how I go about amending the below code to ensure the sum of the column is always obtained from the correct cell?

Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("SFDC download").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveSheet.Range("$A$1:$AX$1818").AutoFilter Field:=45, Criteria1:=Array( _
"Q2", "Q3", "Q4", "="), Operator:=xlFilterValues
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveSheet.Range("$A$1:$AX$1818").AutoFilter Field:=35, Criteria1:= _
"Andrew Kendall"
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("C:C").Select
Range("C65536").Activate
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-65534]C:R[-1]C)"
Columns("C:C").Select
Range("C2").Activate
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 1279
ActiveWindow.ScrollRow = 2451
ActiveWindow.ScrollRow = 7670
ActiveWindow.ScrollRow = 15552
ActiveWindow.ScrollRow = 20877
ActiveWindow.ScrollRow = 26735
ActiveWindow.ScrollRow = 34191
ActiveWindow.ScrollRow = 39836
ActiveWindow.ScrollRow = 44522
ActiveWindow.ScrollRow = 48144
ActiveWindow.ScrollRow = 49635
ActiveWindow.ScrollRow = 50594
ActiveWindow.ScrollRow = 50807
ActiveWindow.ScrollRow = 51552
ActiveWindow.ScrollRow = 53043
ActiveWindow.ScrollRow = 54215
ActiveWindow.ScrollRow = 56984
ActiveWindow.ScrollRow = 59327
ActiveWindow.ScrollRow = 61671
ActiveWindow.ScrollRow = 62203
ActiveWindow.ScrollRow = 62523
ActiveWindow.ScrollRow = 62736
ActiveWindow.ScrollRow = 62842
ActiveWindow.ScrollRow = 62949
ActiveWindow.ScrollRow = 63055
ActiveWindow.ScrollRow = 63588
ActiveWindow.ScrollRow = 64227
ActiveWindow.ScrollRow = 64653
ActiveWindow.ScrollRow = 65079
ActiveWindow.ScrollRow = 65505
Range("C65536").Select
Selection.Copy
Sheets("Malgieri").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
 
Back
Top