I am trying to pull data from an pre complied data dump file. Starting with A10 until three blank rows are detected from the user defined source file. The data is in columns A:AU but only need certain data from within different cells. Need to loop thru the source report to pull from until three blank rows are detected from the user defined source file. I think that I am on the right track in getting this done but not 100% sure if it will work as expected. What needs to occur ( I hope) is to have data pulled based on Product value & placed in a specific format for an report. I have included some of the code and welcome any advise on improving it and getting the expected results.
VBA Code:
' Allow the user to define the filename to be opened
Sub Get_Data_From_File()
' Defines the varibles used
Dim FileToOpen As Variant
Dim OpenBook As Workbook
' Pervent Screen Updating
Application.ScreenUpdating = False
' Request the file to open from the user.
FileToOpen = Application.GetOpenFilenmae(Title:="Browse for your file & Import Range", FileFilter:="Excel Files(*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets(1).Range("A10:AU")
'Not sure if this is needed . . . ThisWorkbook.Workseets("Line 4 Report").Range("A2").PasteSpecial xlPasteValues
' Don't want to change scource file . . . OpenBook.Close False
End If
' Allows Screen updating to occur.
Application.ScreenUpdating = True
End Sub
' pull data starting with A10 until three blank rows are detected from the user defined source file.
' Data is in columns A:AU but only need certain data from different cells
' Source data is in a pre-complied format and needs to have data pulled based on Product value & placed
' in a specific format for line report.
Sub PullData()
' Defines Variables to be used
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Line 4 Report")
' Searches for the next blank row for data entry
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
If FileToOpen.Cells(iRow, 8).Value = "Product1" Then
.Cells(iRow, 1).Value = FileToOpen.Cells(iRow, 1).Value
.Cells(iRow, 2).Value = FileToOpen.Cells(iRow, 2).Value
.Cells(iRow, 3).Value = FileToOpen.Cells(iRow, 8).Value
.Cells(iRow, 4).Value = FileToOpen.Cells(iRow, 9).Value
.Cells(iRow, 5).Value = FileToOpen.Cells(iRow, 4).Value
.Cells(iRow, 6).Value = FileToOpen.Cells(iRow, 14).Value
.Cells(iRow, 7).Value = FileToOpen.Cells(iRow, 18).Value
.Cells(iRow, 8).Value = FileToOpen.Cells(iRow, 20).Value
.Cells(iRow, 9).Value = FileToOpen.Cells(iRow, 21).Value
.Cells(iRow, 10).Value = FileToOpen.Cells(iRow, 22).Value
.Cells(iRow, 11).Value = FileToOpen.Cells(iRow, 23).Value
.Cells(iRow, 12).Value = FileToOpen.Cells(iRow, 50).Value
.Cells(iRow, 13).Value = FileToOpen.Cells(iRow, 30).Value
.Cells(iRow, 14).Value = FileToOpen.Cells(iRow, 28).Value
.Cells(iRow, 15).Value = FileToOpen.Cells(iRow, 29).Value
.Cells(iRow, 16).Value = FileToOpen.Cells(iRow, 31).Value
.Cells(iRow, 17).Value = FileToOpen.Cells(iRow, 32).Value
.Cells(iRow, 18).Value = FileToOpen.Cells(iRow, 36).Value
.Cells(iRow, 19).Value = FileToOpen.Cells(iRow, 41).Value
.Cells(iRow, 20).Value = FileToOpen.Cells(iRow, 19).Value
.Cells(iRow, 21).Value = FileToOpen.Cells(iRow, 44).Value
.Cells(iRow, 22).Value = FileToOpen.Cells(iRow, 45).Value
.Cells(iRow, 23).Value = FileToOpen.Cells(iRow, 47).Value
.Cells(iRow, 24).Value = FileToOpen.Cells(iRow, 46).Value
.Cells(iRow, 25).Value = FileToOpen.Cells(iRow, 48).Value
ElseIf FileToOpen.Cells(iRow, 8).Value = "Product2" Then
.Cells(iRow, 1).Value = FileToOpen.Cells(iRow, 1).Value
.Cells(iRow, 2).Value = FileToOpen.Cells(iRow, 2).Value
.Cells(iRow, 3).Value = FileToOpen.Cells(iRow, 8).Value
.Cells(iRow, 4).Value = FileToOpen.Cells(iRow, 9).Value
.Cells(iRow, 5).Value = FileToOpen.Cells(iRow, 4).Value
.Cells(iRow, 6).Value = FileToOpen.Cells(iRow, 14).Value
.Cells(iRow, 7).Value = FileToOpen.Cells(iRow, 18).Value
.Cells(iRow, 8).Value = FileToOpen.Cells(iRow, 20).Value
.Cells(iRow, 9).Value = FileToOpen.Cells(iRow, 21).Value
.Cells(iRow, 10).Value = FileToOpen.Cells(iRow, 22).Value
.Cells(iRow, 11).Value = FileToOpen.Cells(iRow, 23).Value
.Cells(iRow, 12).Value = FileToOpen.Cells(iRow, 50).Value
.Cells(iRow, 13).Value = FileToOpen.Cells(iRow, 30).Value
.Cells(iRow, 14).Value = FileToOpen.Cells(iRow, 28).Value
.Cells(iRow, 15).Value = FileToOpen.Cells(iRow, 29).Value
.Cells(iRow, 16).Value = FileToOpen.Cells(iRow, 31).Value
.Cells(iRow, 17).Value = FileToOpen.Cells(iRow, 32).Value
.Cells(iRow, 18).Value = FileToOpen.Cells(iRow, 36).Value
.Cells(iRow, 19).Value = FileToOpen.Cells(iRow, 41).Value
.Cells(iRow, 20).Value = FileToOpen.Cells(iRow, 19).Value
.Cells(iRow, 21).Value = FileToOpen.Cells(iRow, 44).Value
.Cells(iRow, 22).Value = FileToOpen.Cells(iRow, 45).Value
.Cells(iRow, 23).Value = FileToOpen.Cells(iRow, 47).Value
.Cells(iRow, 24).Value = FileToOpen.Cells(iRow, 46).Value
.Cells(iRow, 25).Value = FileToOpen.Cells(iRow, 48).Value
' . . . continues for a total of eight products
End If
End With
End Sub