Thread: Macro/VBA needed for opening excel file

Why not just rename your tube positions to those in your file i.e put a 0 in and use VLookup?

Please provide a few examples of the files names that will contain the data for Tube Received and Tube Sent and where will both these file names be saved in the macro template?

In the macro template, would you be putting the entire path in any cell or just the filename? If the filename, in which cell would it be?

We lost the files that you attached due to some small issue in the upgrade. Can you please attach the files again?

I just needed one last clarification before i work on your issue (I know its been a while, but surely i should have a good solution this time).
You would be inputting the file name for tube Sent in B8 and for tube received in B9 right?

I apologize for the delay. I have got the code ready for you. Try this.

Option Explicit
Dim lrow As Long
Dim SentPath As String
Dim RecPath As String
Dim lastrow As Long
Dim i As Long
Dim FName As String

Sub populate_file()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

lrow = ThisWorkbook.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
SentPath = "D:\Documents and Settings\DWS\DWS\Tubes Sent\"
RecPath = "D:\Documents and Settings\DWS\DWS\Tubes Received\"
'SentPath = "C:\Documents and Settings\Tubes Sent\"
'RecPath = "C:\Documents and Settings\Tubes Received\"

For i = 8 To lrow Step 40
    
    'TUBES SENT
    Workbooks.Open SentPath & ThisWorkbook.Worksheets(1).Range("B" & i).Value
    FName = ActiveWorkbook.Name
    
    Workbooks(FName).Worksheets(1).Sort.SortFields.Clear
    Workbooks(FName).Worksheets(1).Sort.SortFields.Add Key:=Range("A:A") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Workbooks(FName).Worksheets(1).Sort
        .SetRange Range("A:B")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    lastrow = Workbooks(FName).Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
    Workbooks(FName).Worksheets(1).Range("B1:B" & lastrow).Copy
    ThisWorkbook.Worksheets(1).Range("C" & i + 5).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:= _
        False, Transpose:=False
    Workbooks(FName).Close
    
    'TUBES RECEIVED
    Workbooks.Open RecPath & ThisWorkbook.Worksheets(1).Range("B" & i).Value
    FName = ActiveWorkbook.Name
    Workbooks(FName).Worksheets(1).Sort.SortFields.Clear
    Workbooks(FName).Worksheets(1).Sort.SortFields.Add Key:=Range("A:A") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Workbooks(FName).Worksheets(1).Sort
        .SetRange Range("A:B")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    lastrow = Workbooks(FName).Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
    Workbooks(FName).Worksheets(1).Range("B1:B" & lastrow).Copy
    ThisWorkbook.Worksheets(1).Range("D" & i + 5).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:= _
        False, Transpose:=False
    Workbooks(FName).Close

Next i

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Note: You can have as many pack IDs as you want in this one file, you just need to ensure that one pack id is placed 40 rows below the other (as i have programmed it this way). In this report, I inserted a row before the 2nd pack id, so that the 2nd pack id is at row 48, since the 1st one is on row 8.

Do let me know if you face any issues.