See if the following code does the task for you:-
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
.AutoFilter 1, dSrch
Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
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:-
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.