Searching many sheets for data

[Klartigue]

Please see the attached document. On Sheet 1, I would like to create a search page in which I can enter a month, or "All", which will define what sheets to search.

For instance, If in cell B3 on sheet 1 is "January" then that references sheet "January 2006." If in cell B3 on sheet 1 is "All" then that references sheet "January 2006" and sheet "February 2006".

Then based on that search zone in cell B3, I would like to be able to either enter an address in cell B4 or a zipcode in cell B5 and have a macro be able to search for either that address or zipcode based on the Month, or All defined by cell B3, and then copy and paste that data row (from column A to H) onto sheet 1 starting in row 10.

There is more explanation on the excel sheet.

Any ideas on how to start building this?

I was thinking i could even enter the Month, and zipcode or address, and then have a macro run that does a vlookup or something on the designated sheets?

[Klartigue]

Or lets just start small. How can I say if value in B5 is "January" then search "January 2006" sheet or If value in B5 is "February" then search "February 2006" sheet, or If value in B5 is "All", then search "January 2006" sheet and "February 2006" sheet?

[Klartigue]

this is what I have so far:

Sub Search1()
'

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow

If .Cells(i, "B").Value = "January" Then

Sheets("January 2006").Select

End If

Next i

End With
End Sub
Sub PulldataZipcode()

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 4 To lastrow

If .Cells(i, "F").Value = "HOW DO I REFERENCE THE CELL B7 ON SHEET 1? " Then


With Range(.Cells(i, "A"), .Cells(i, "H")).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("January 2006").Select

End With

End If

Next i

End With

End Sub

[Klartigue]

Something like this?


Sub PulldataZipcode()

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 4 To lastrow

If .Cells(i, "F").Value = "Sheet1!B7" Then


With Range(.Cells(i, "A"), .Cells(i, "H")).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A10000").End(xlUp).Offset(10, 0).Select
ActiveSheet.Paste
Sheets("January 2006").Select

End With

End If

Next i

End With

End Sub

[CatDaddy]

If .Cells(i, "F").Value = Sheets(1).Range("B7").Value Then

[Klartigue]

Thanks. On the sheet I attached, I am trying this code when cell B5 on sheet 1 says "January" and cell B7 on sheet 1 is "75204".

But the code is not pulling the correct lines from the January 2006 sheet and I cannot figure out why. It should be pulling the rows (from columns A:H) in which cells in column F has "75204"?

Sub Search1()
'

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow

If .Cells(i, "B").Value = "January" Then

Sheets("January 2006").Select

End If

Next i

End With
End Sub
Sub PulldataZipcode()

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 4 To lastrow

If .Cells(i, "F").Value = Sheet1.Range("B7").Value Then


With Range(.Cells(i, "A"), .Cells(i, "H")).Select
Selection.Copy
Sheets("Sheet 1").Select
Range("A10000").End(xlUp).Offset(3, 0).Select
ActiveSheet.Paste
Sheets("January 2006").Select

End With

End If

Next i

End With

End Sub