sheet references VBA help

[fable]

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!!

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 Sub

p.s. I know the code is ugly & long winded, but let me get used to it first haha

Thanks!

~ Fable

 

[fable]

Would still love some assistance with this guys.....