Look up tables across workbookClosed

Question

Hello there!

I am currently working on a spreadsheet for my organization. As far as the data is concerned, it is collected by another part of the organization as a table for each day it is then entered on a worksheet reflecting the corresponding week.  These tables are around 10 rows high and 16 columns across. I am attempting to streamline looking up the entry for a particular day and want to be able to enter the date in a cell on a separate worksheet and have the data reflect in a similar table below. I have been experimenting with VLOOKUP and INDEX with little success.

In other words...

Enter Date in Cell

Search All Worksheets in Workbook for Table matching that Date

Display all data in table for date in new table.


Maybe I am just using the wrong functions but I cannot seem to figure this one out. Any help would be appreciated.

Thank you!

vcoolio · Answer

Hello MACrucible,

See if the following code does the task for you:-

Sub FindData()

Dim ws As Worksheet
        Dim dSrch As String
        
dSrch = Sheet1.[A1].Value
If dSrch = "" Then Exit Sub

Application.ScreenUpdating = False

For Each ws In Worksheets
      If ws.Name <> "Sheet1" Then
With ws.[A1].CurrentRegion
             .AutoFilter 1, dSrch
             .Offset(1).EntireRow.Copy
             Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
             .AutoFilter
             End With
       End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I've made the following assumptions:-

- Sheet1 (the destination sheet) has a search box in cell A1.
- Sheet 1 headings are in row 2.
- Source sheet headings are in row 1 with data commencing in row 2.
- Source sheet dates are in Column A.

The code basically filters Column A of each worksheet for the date placed in cell A1 of the main (destination) sheet and then transfers the relevant row of data to the destination sheet.

I've attached a little sample for you to play with at the following link:-

http://ge.tt/6dRfyhp2

Place a date (based on the ones you see in the two source sheets) in cell A1 of the main sheet then click on "GO" to see the code at work.

I hope that this helps.

Cheerio,
vcoolio.

Look up tables across workbook

1 response

Similar discussions

Subscribe To Our Newsletter!