Macro: Copy and Paste Cells

[AntiPivotTable]

Hi everyone,

So I have a code that copies info from several identically structured downloaded worksheets onto a master sheet. However, I can't seem to edit the code properly so that I can add 4 more columns of info, in a specific order, to the master sheet .

Im attaching the downloaded worksheet with my current macro as is. Note, that the macro is written so it can extract the info from several downloaded worksheets in a folder and extract the info in the deserted format to a master sheet.

What I need is: Divide cell B2 in every downloaded sheet (like the one I attached) into three parts (Country, Month and Year). Then put that divided information into my mastersheet with Country name in Column A, Year in Column B and Month in Column C. Then these three columns, in the mastersheet, need to be copied down to the row level to which Column E is populated.

The Last thing is to put the information of cell A4 from the downloaded sheet into Column D of the master sheet, and once again copy it down until the row level of Column E.

Any help or tips would be great! Thanks!

Here is the downloaded sheet (incl. my macro): http://www.filedropper.com/downloadedworsheet

The Code is here:

[FONT=monospace]Sub HIADataConvert()[/FONT]
<code style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; font-style: inherit; font-weight: inherit; font-family: monospace; line-height: 12px; ">DirectoryPath = "/Users/xxxxx"    Set FSO = CreateObject("Scripting.FilesystemObject")    Set FileList = FSO.GetFolder(DirectoryPath)    Dim Druglist()    Set MasterBook = Worksheets.Add    Range("A1").Resize(1, 7) = Array("Medicine", "Dosage Strength", "Dosage Type", "Type", "Price Type", "Price Value", "Availability")    For Each FileItem In FileList.Files            If InStr(UCase(FileItem.Name), "XLS") Then                    Set CloseMe = Workbooks.Open(FileItem.Path, ReadOnly:=True)                    CloseMe.Activate                    EndRow = Range("A" & Rows.Count).End(xlUp).Row                    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 ' OEM vs Generics                            End If                    Next 'drug            End If            CloseMe.Close            MasterBook.Activate            Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(UBound(Druglist) + 1, UBound(Druglist, 2) + 1) = Druglist    Next 'file    MasterBook.Activate    Columns("A:D").Insert    Range("A1").Resize(1, 4) = Array("Country", "Year", "Month", "Transaction")End Sub

</code></pre>