Yep it makes perfect sense. I understand you cant send your spreadsheet due to proprietary information. I am in the same situation. So, here is what you need to do. I assumed you would be using .mdb access file format and we will use the package number as the parameter for example purposes. You can apply this logic to any of the three tabs of data. We will stick with one for example. Once you get this to work you will see how you can apply this to multiple situations to make your life easier.
Step 1.
Make your database and put the data you are currently storing on those three tabs in excel into access data tables. For ease of explanation you can simply make the access tables identical to your excel data tabs. (or not up to you) Record your database name and database file location path for reference at step 3.
Step 2.
Make a parameter query in access to pull the data you normally would pull from one of the sheets using your package number as the parameter. Run the query manually and enter a known package number and ensure it pulls data. If it does, record your query name for the next step. If not stop and fix your query until it does.
Step 3.
In your excel main/master page add a command button next to where your package number is entered and attach the following code to it. You will need to modify the spots Ive indicated with your information above.
Code:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
'step 1 declare variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
'step 2 open database
Set MyDatabase = DBEngine.OpenDatabase _
("C:\yourdatafolderhere\youraccessdbhere.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("yourquerynamehere")
'step 3 paramter value for your query (I assume this is the sheet and cell where your package number is entered.
With MyQueryDef
.Parameters("[Enter]") = ThisWorkbook.Sheets("yoursheettabhere").Range("yourreferencecellhere").Value
End With
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 4: Clear previous contents - This is one of your 3 sheet tabs where you would normally store your data.
ThisWorkbook.Sheets("yoursheettab1").Select
'enter the entire range of where your data is below
Range("A1:bqq10000").ClearContents
'Step 5: Copy the recordset to Excel. Row 2 is where the data will be placed
ActiveSheet.Range("A2").CopyFromRecordset MyRecordset
'Step 6:Puts the database data headers in
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
Next i
Application.ScreenUpdating = True
'homes your spreadheet - remove if not needed or select any page you want to select when code is done running
ThisWorkbook.Sheets("yourmainpgetabhere").Select
Range("$a$1").Select
MsgBox "package data completed"
End Sub
Step 4.
Enter your package number into your cell as you normally do and click the command button. This will query the database for your package number information and place it on the page you indicated in the code above.
Step 5.
You can now change your vlookups on your master page to simply look at the data you just newly queried and entered into excel and everytime you change the package number and re-run your data on your master page will update.
Step 6.
Apply the same logic to your other two pages of data you need. (you can also add more parameters into the same query and even possibly grab the data for all three tabs with one click.)
Thats it! Seems complicated at first but once you figure it out you will use it all the time. - good luck!
P.S. - if you have your package numbers already in the database you can also query the database for distinct package numbers and populate a combobox. Then all you need to do is use the pulldown to get the package number and hit run. But get the above working first before you try doing this.
