Apply Macro to all files in a specific folder.

So I have a macro to extract a lot of data from an online database that unfortunately only allowed me to do so in a complete useless format (like this: http://imgur.com/v6bZQc4). Once you extract to excel, with the option they provide, it looks like this (for the sake of this example I deleted most rows): http://imgur.com/7Hec8dn

My macro flattens out the data to more useable format. However, I would now like to have the macro run over a folder that contains all the downloaded worksheets (the ones in the unusable format) and then generate one master sheet (in the desired format specified) from all of the files in this folder.

Some issues:

1) The second part of my macro, which adds columns for: "Country" "Month" "Year" "Transaction" are linked to a sheet1. I believe this would bring up errors and there might be a better.

2) In the master sheet generated headers must remain the same in a way that the data generated by running the macro on each downloaded sheet goes underneath.

I've tried fixing these issues and getting the macro to apply to all files in a specific folder, so far no luck. I was told to use Scripting.Filesystemobject, but I it didn't work for me - or more likely, I didn't use it properly.

Thanks in advance for any help. If this makes it easier, here's one of the downloaded worksheets and the macro in question: http://www.filedropper.com/example_2

Macro in question.

PHP Code: 

Attribute VB_Name = "HIADataImporter"
Sub HIADataConvert()
EndRow = Range("A" & Rows.Count).End(xlUp).Row
    Dim Druglist()

ReDim Druglist(EndRow * 6, 6)

For i = 1 To EndRow
        If Cells(i, 1).Value = "Medicine" Then
                StartRow = i
                Cells(i, 1).Offset(1, 0).Activate
                Exit For
        End If
Next


DrugIterator = 0
For i = ActiveCell.Row To EndRow
        If Cells(i, 1).Value <> "" Then
                Cells(i, 1).Activate
                DrugName = Left(Cells(i, 1).Value, InStr(Cells(i, 1).Value, "-") - 2)
                DosageType = Right(Cells(i, 1).Value, Len(Cells(i, 1).Value) - InStrRev(Cells(i, 1).Value, " "))
                Strength = Mid(Cells(i, 1).Value, Len(DrugName) + 4, Len(Cells(i, 1).Value) - Len(DrugName) - Len(DosageType) - 3)

                For k = 0 To 1
                        ActiveCell.Offset(1, 0).Activate
                        If ActiveCell.Offset(0, 1).Value <> "" Then
                                For J = 0 To 2
                                        Druglist(DrugIterator + J, 0) = DrugName
                                        Druglist(DrugIterator + J, 1) = Strength
                                        Druglist(DrugIterator + J, 2) = DosageType
                                        Druglist(DrugIterator + J, 3) = ActiveCell.Offset(0, 1).Value
                                        Druglist(DrugIterator + J, 4) = Cells(StartRow, J + 4).Value
                                        Druglist(DrugIterator + J, 5) = ActiveCell.Offset(0, J + 2).Value
                                        Druglist(DrugIterator + J, 6) = ActiveCell.Offset(0, 5).Value
                                Next
                                DrugIterator = DrugIterator + J
                        End If
                Next

        End If
Next

Worksheets.Add
Range("A2").Resize(UBound(Druglist) + 1, UBound(Druglist, 2) + 1) = Druglist
Range("A1").Resize(1, 7) = Array("Medicine", "Dosage Strength", "Dosage Type", "Type", "Price Type", "Price Value", "Availability")
Columns("A:G").AutoFit
Attribute CountryDatePriceType.VB_ProcData.VB_Invoke_Func = " \n14" ' ' CountryDatePriceType
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Country"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.FormulaR1C1 = "Year"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Month"
Range("D1").Select
Columns("D:D").ColumnWidth = 14
ActiveCell.FormulaR1C1 = "Transaction"
Range("A2").Select
Sheets("Sheet1").Select
ActiveWindow.SmallScroll Down:=-322
Range("A2:F2").Select
With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = True
End With
Selection.Merge True
Range("A2").Select
Sheets("Sheet1").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
Columns("A:A").ColumnWidth = 20.67
Range("A3").Select
Application.CutCopyMode = False
Range("A2").Select
Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=",", FieldInfo:=Array(Array(1, 1), Array(2, 1))
Range("B2").Select
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
    :=",", FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3, 1))
Range("D2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[-3]"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("D3").Select
Selection.AutoFill Destination:=Range("D3:D247")
Range("D3:D247").Select
Range("C3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C247")
Range("C3:C247").Select
Range("B3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B247")
Range("B3:B247").Select
Range("A3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A247")
Range("A3:A247").Select
Range("B6").Select
ActiveWindow.SmallScroll Down:=63
Range("M94").Select
ActiveWindow.SmallScroll Down:=-275
MsgBox "HIA Data was copied in the new format"
End Sub