'deploy a macro on several excel files in a folder'
Sub main_sub()
Set ThisWB = ActiveWorkbook 'to refer to the original excel'
PathName = "C:\excel macro test\"
Filename = Dir(PathName & "*.csv")
Do While Filename <> ""
Set CurrentWorkbook = Workbooks.Open(PathName & Filename) 'to refer to and write in the newly open file'
' do your stuff
Call a
Call SortMonthly
Filename = Dir()
Loop
End Sub
Sub a()
'
' a Macro
'
' Keyboard Shortcut: Ctrl+g
'
'Range("A3:A").Select
'Keyboard Shortcut: Ctrl g
'
Range("A3:A21100").Select
Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
End Sub
Sub SortMonthly()
SheetName = ActiveSheet.Name
i = 3
Sm = 0
r = 4
Fin = True
YR = ActiveSheet.Range("A" & i)
ActiveSheet.Range("E" & i).Select
ActiveCell.FormulaR1C1 = YR
While Fin
While YR = Year(ActiveSheet.Range("A" & i))
M = Month(ActiveSheet.Range("A" & i))
While Month(ActiveSheet.Range("A" & i)) = M
Sm = Sm + ActiveSheet.Range("C" & i)
i = i + 1
Wend
ThisWB.Worksheets("Sheet1").Range("E" & r).Select
ActiveCell.FormulaR1C1 = Sm
Mo = MonthName(M)
ThisWB.Worksheets("Sheet1").Range("D" & r).Select
ActiveCell.FormulaR1C1 = Mo
r = r + 1
Sm = 0
Wend
r = r + 1
YR = Year(ActiveSheet.Range("A" & i))
ActiveSheet.Range("E" & r).Select
ActiveCell.FormulaR1C1 = YR
r = r + 1
If ActiveSheet.Range("A" & i) = "" Then Fin = False
Wend
End Sub
'continue with loop in folder
Bookmarks