AntiPivotTable
New Member
- Joined
- Apr 29, 2013
- Messages
- 6
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:
</code></pre>
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:
Code:
[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