Hi,
This assumes history tabs are sorted by name.


HTH

Sub CustomerHistory()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng As Range
Dim shName As String, CustName As String

Set ws1 = Worksheets("Cust info")
'Clear form
ws1.Range("a4:z100").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents

shNames = Array("Job History", "Invoice History", "Estimate History")

nextrow = 4

For i = 0 To 2

shName = shNames(i)
CustName = ws1.Cells(2, 2)

Set ws2 = Worksheets(shName)
With ws2
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
Set rng = .Range("b2:b" & lastrow)
End With

ws1.Cells(nextrow, 1) = shName
nextrow = nextrow + 1
ws2.Rows(1).EntireRow.Copy ws1.Cells(nextrow, 1)

res = Application.Match(CustName, rng, 0)
If IsError(res) Then
MsgBox "There is no " & shName & " for customer " & CustName
nextrow = nextrow + 2
Else
srow = res + 1
nrow = Application.CountIf(rng, CustName)
nextrow = nextrow + 1
ws2.Cells(srow, 1).Resize(srow + nrow - 1).Resize(nrow, 12).Copy
ws1.Cells(nextrow, 1)
nextrow = nextrow + nrow + 1
End If

Next i
End Sub


"cbrd" wrote:


I have the following workbook and probably should have this done in
Access, but Excel is problem enough for me right now:

http://www.ashleylandscaping.com/sample.xls

On the "Cust Info" sheet, and when I select a name in the drop down
list, it fills in the Cust ID number above. When I have a number in the
Cust ID box and click the "Get Customer History" button, It should then
look for that Cust ID # in the three different worksheets named "Job
History, Invoice History, and Invoice History". It should then copy The
Column Heading for each of those sheets and any row with a matching Cust
ID.

Ive got other worksheets with macros copying and pasting rows and such,
but I couldnt figure out how to copy column heading and the rows. Also I
couldnt figure out how to make it paste info without overwriting
previous rows because of the variance in the number of rows that may be
imported from each of the 3 sheets.

Heres what it looks like now:
http://www.ashleylandscaping.com/now.jpg

And the result should look something like this:
http://www.ashleylandscaping.com/want.jpg


--
cbrd
------------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=499618