My task is to create a Macro in Workbook 1 that can pull in data from Workbook 2. The ask is that I create a formula in the VBA that consists of applying the Filter/Filter/Offset formula in Workbook 1 that can pull in the data from Workbook 2. There are of course specific ranges associated with both Workbooks.

I can successfully write the Macro in Workbook1 to allow the user to “steer” to the source data in Workbook2 and pull in specific columns and rows of data. But the ask is more complicated than that. The Filter/Filter/Offset is specifically required.

I can manually write the formula in Cell “A1” of Workbook1 to pull in the data range from Workbook2 with 100% success.

=FILTER(FILTER(OFFSET('[Glass Order Form Template.xlsm]Glass Schedule'!$C$8,0,0,3001,16),'[Glass Order Form Template.xlsm]Glass Schedule'!$D$8:$D$3008<>""),{1,1,1,1,0,0,1,1,0,0,0,0,0,0,1,1})

If the source data were in the same workbook I think I would have no problem completing the task. However, since the source data is located in Workbook2 I have hit the wall. I just don’t know how to write the formula in VBA to accommodate pulling the data from Workbook2 when it’s being referenced from within the formula. I tried assigning a variable and substituting the variable name in lieu of the workbook2/worksheet name but I botched that. If anyone has any ideas I would appreciate the lesson.

File Name of Data Source Workbook2: Glass Order Form Template.xlsm
Sheet Name of Data Source Worksheet: Glass Schedule

Thanks in advance. Here is my code:

Sub ImportData()
    Dim dataFilePath As String
    Dim dataWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim AnswerYes As String
    Dim AnswerNo As String
    
    Dim lastRow As Long


    ' Prompt user to select data file
    dataFilePath = Application.GetOpenFilename("Excel Files (*.xlsm), *.XLSM")
    If dataFilePath = "False" Then Exit Sub ' User canceled the selection

    ' Open the data workbook
    Set dataWorkbook = Workbooks.Open(dataFilePath)

    ' Data is in "Glass Schedule" worksheet, and range is non-contiguous columns
    
    'Define worksheet variables
    Dim sourceSheet As Worksheet    'Glass Take Off Sheet
    Dim targetSheet As Worksheet    'Glass QC Sheet
    
    'Define Range variable
    Dim destination As Range
    
    ' Set Source and Target worksheets
    Set sourceSheet = dataWorkbook.Worksheets("Glass Schedule")
    Set targetSheet = ThisWorkbook.Worksheets("QC")
    
    '---------------------------------------------------------------------------
    'Copy from Glass Take Off workwheet and paste to QC Worksheet
    '---------------------------------------------------------------------------
    
    sourceSheet.Range("D1").Copy                        'Job Number
        targetSheet.Range("B2").PasteSpecial xlValues

    targetSheet.Range("B3").UnMerge                     'Unmerge Job Name field, if merged.

    sourceSheet.Range("D2").Copy                        'Job Name
        targetSheet.Range("B3").PasteSpecial xlValues
        
    With targetSheet
    
    .Range("A7").Formula = "=FILTER(FILTER(OFFSET('[Glass Order Form Template.xlsm]Glass Schedule'!$C$8,0,0,3001,16),'[Glass Order Form Template.xlsm]Glass Schedule'!$D$8:$D$3008<>""),{1,1,1,1,0,0,1,1,0,0,0,0,0,0,1,1})"
    
    End With

    ' --------------------------------------------------------------------------
    ' Dialogue Box and User Input
    ' --------------------------------------------------------------------------
 AnswerYes = MsgBox("Close Glass Take Off Sheet?", vbQuestion + vbYesNo, "User Repsonse")
 If AnswerYes = vbYes Then
        dataWorkbook.Close
    Else
        Exit Sub
 End If

End Sub