This might be an option. configure a query with where >[enter invoice start] and <[enter invoice end]
in the code below there is a new parameter for cell M2
Code:
Sub RunCNC_Restock()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase("C:\Documents and Settings\USER\My Documents\DATABASE.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("query name")
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[enter invoice start]") = Range("L2").Value
.Parameters("[enter invoice end]") = Range("M2").Value
End With
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 5: Clear previous contents
Sheets("C & C restock").Select
ActiveSheet.Range("U20:Z27").ClearContents
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("U21").CopyFromRecordset MyRecordset
'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(20, i + 20).Value = MyRecordset.Fields(i - 1).Name
Next i
'MsgBox "Your Query has been Run"
End Sub
Bookmarks