Hi all,
I'm a complete, total and utter novice where VBA & Macros are concerned. I can record I macro, and I can go look at the supporting data - tweak the odd bit here and there but that is where my knowledge falls flat on it's proverbial face.
My issue: I've recorded a macro that I want to on the January work sheet , using the January supporting data from the sheet that's to the right of it. Now, I need to amend this macro up to working on the next month & it's supporting data. These sheets do not have succinct sheet references [i.e. the (Name) field in the editor is not like Sheet 4 is current and sheet 3 is the next one along].
The Macro: the aim is for me to be on the month I want the macro to run on. For the macro to go into the supporting sheet, add a column of calculation. Go to the main month sheet, add in a formula in certain columns. These columns do not change across the spreadsheet.
I'm sure that I don't need to set up a macro for every month of the year!!
any help would be appreciated!!
p.s. I know the code is ugly & long winded, but let me get used to it first haha
Thanks!
~ Fable
I'm a complete, total and utter novice where VBA & Macros are concerned. I can record I macro, and I can go look at the supporting data - tweak the odd bit here and there but that is where my knowledge falls flat on it's proverbial face.
My issue: I've recorded a macro that I want to on the January work sheet , using the January supporting data from the sheet that's to the right of it. Now, I need to amend this macro up to working on the next month & it's supporting data. These sheets do not have succinct sheet references [i.e. the (Name) field in the editor is not like Sheet 4 is current and sheet 3 is the next one along].
The Macro: the aim is for me to be on the month I want the macro to run on. For the macro to go into the supporting sheet, add a column of calculation. Go to the main month sheet, add in a formula in certain columns. These columns do not change across the spreadsheet.
I'm sure that I don't need to set up a macro for every month of the year!!
any help would be appreciated!!
Code:
Sub autofill()'
' autofill Macro
'
'
Sheets("Jan from Alp").Select
Columns("N:N").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("N1").Select
ActiveCell.FormulaR1C1 = "Dis Vat"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=ROUND(RC[-3]+0.02,1)/10"
Range("N2").Select
Selection.autofill Destination:=Range("N2:N65"), Type:=xlFillDefault
Range("N2:N65").Select
ActiveWindow.SmallScroll Down:=-63
Sheets("January").Select
Range("F5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUMPRODUCT(SUMIFS('Jan from Alp'!C[5],'Jan from Alp'!C3,January!RC2,'Jan from Alp'!C18,January!RC3)),"""")"
Range("F5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUMPRODUCT(SUMIFS('Jan from Alp'!C[5],'Jan from Alp'!C3,January!RC2,'Jan from Alp'!C18,January!RC3)),"""")"
Range("G5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUMPRODUCT(SUMIFS('Jan from Alp'!C[5],'Jan from Alp'!C3,January!RC2,'Jan from Alp'!C18,January!RC3)),"""")"
Range("H5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUMPRODUCT(SUMIFS('Jan from Alp'!C[6],'Jan from Alp'!C3,January!RC2,'Jan from Alp'!C18,January!RC3)),"""")"
Range("F5:H5").Select
Selection.autofill Destination:=Range("F5:H10"), Type:=xlFillDefault
Range("F5:H10").Select
Range("F10:H10").Select
Selection.Copy
Range("F13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.autofill Destination:=Range("F13:H32"), Type:=xlFillDefault
Range("F13:H32").Select
Sheets("Jan from Alp").Select
ActiveWindow.SmallScroll Down:=-15
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
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
Sheets("January").Select
Range("F32").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUMPRODUCT(SUMIFS('Jan from Alp'!C[5],'Jan from Alp'!C3,January!RC2,'Jan from Alp'!C18,January!RC3)),"""")"
Range("G25").Select
ActiveWindow.SmallScroll Down:=-42
Range("F5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUMPRODUCT(SUMIFS('Jan from Alp'!C[5],'Jan from Alp'!C3,January!RC2,'Jan from Alp'!C19,January!RC3)),"""")"
Range("G5").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUMPRODUCT(SUMIFS('Jan from Alp'!C[5],'Jan from Alp'!C3,January!RC2,'Jan from Alp'!C19,January!RC3)),"""")"
Range("H5").Select
ActiveWindow.SmallScroll Down:=-3
ActiveCell.FormulaR1C1 = _
"=IFERROR(SUMPRODUCT(SUMIFS('Jan from Alp'!C[6],'Jan from Alp'!C3,January!RC2,'Jan from Alp'!C19,January!RC3)),"""")"
Range("F5:H5").Select
Selection.autofill Destination:=Range("F5:H10"), Type:=xlFillDefault
Range("F5:H10").Select
Range("F10:H10").Select
Selection.Copy
Range("F13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.autofill Destination:=Range("F13:H32"), Type:=xlFillDefault
Range("F13:H32").Select
Range("F32:H32").Select
Selection.Copy
Range("F35").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Selection.autofill Destination:=Range("F35:H42"), Type:=xlFillDefault
Range("F35:H42").Select
Range("F42:H42").Select
Selection.Copy
Range("F45").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.autofill Destination:=Range("F45:H48"), Type:=xlFillDefault
Range("F45:H48").Select
ActiveWindow.SmallScroll Down:=18
Range("F48:H48").Select
Selection.Copy
Range("F51").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.autofill Destination:=Range("F51:H54"), Type:=xlFillDefault
Range("F51:H54").Select
Range("F54:H54").Select
Selection.Copy
Range("F57").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.autofill Destination:=Range("F57:H60"), Type:=xlFillDefault
Range("F57:H60").Select
Range("F60:H60").Select
Selection.Copy
Range("F63").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=18
Application.CutCopyMode = False
Selection.Copy
Range("F66").Select
ActiveSheet.Paste
Range("F69").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.autofill Destination:=Range("F69:H71"), Type:=xlFillDefault
Range("F69:H71").Select
Range("F71:H71").Select
Selection.Copy
Range("F74").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.autofill Destination:=Range("F74:H86"), Type:=xlFillDefault
Range("F74:H86").Select
Range("F86:H86").Select
Selection.Copy
Range("F89").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.autofill Destination:=Range("F89:H96"), Type:=xlFillDefault
Range("F89:H96").Select
ActiveWindow.SmallScroll Down:=9
Range("F96:H96").Select
Selection.Copy
Range("F99").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.autofill Destination:=Range("F99:H100"), Type:=xlFillDefault
Range("F99:H100").Select
Range("F101").Select
ActiveWindow.SmallScroll Down:=15
End Subp.s. I know the code is ugly & long winded, but let me get used to it first haha
Thanks!
~ Fable
Last edited:
