Copy rows from different worksheets

[cbrd]

I have the following workbook and probably should have this done i
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 dow
list, it fills in the Cust ID number above. When I have a number in th
Cust ID box and click the "Get Customer History" button, It should the
look for that Cust ID # in the three different worksheets named "Jo
History, Invoice History, and Invoice History". It should then copy Th
Column Heading for each of those sheets and any row with a matching Cus
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
couldnt figure out how to make it paste info without overwritin
previous rows because of the variance in the number of rows that may b
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.jp

 

[Toppers]

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

 

[Toppers]

Sorry a minor typo:

Should be (although original works!):

ws2.Cells(srow, 1).Resize(nrow, 12).Copy ws1.Cells(nextrow, 1)

 

[cbrd]

Thanks! thats almost it. The only problem is that when I select a few o
the names (smith and jones for example), it is inserting rows that don
contain their Cust ID #. I reuploaded the .xls so you can see what
mean.

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

I appreciate the help, now I can sit and stare at the screen for
hours (and still wont get it) to try and figure out what you did an
how it works

 

[cbrd]

I tried it again with different items in the history sheets. It seems to
only do this to the first two names in the drop down list. The first
person will always have one row from the second name in the drop down
list, and vice versa. Once you get to the third name in the list,
everything works well. No idea why.

 

[Roger Govier]

Hi

I think you will see that Toppers wrote in his first posting

This assumes history tabs are sorted by name.

If you sort your sheets by name first, I think you will find that it
will work.
You could build the sort into the VBA code before running it.
If the existing order is important, create an additional column at the
right of each sheets data, numbered 1,2 3,4 etc. and re-sort by this
column after the routine has completed its task.

 

[Toppers]

Re my original posting: I works by ASSUMING(!) that history sheets are sorted
by NAME. As the "Job History" tab isn't, you will get the strange results. If
sorting by NAME is not acceptable, we will need a slightly different solution.

If there is only ONE occurrence of a name it will (appear) to work!

HTH

 

[cbrd]

Thanks you two ...works great

 

[Roger Govier]

Hi

The thanks are entirely due to Toppers. He did the hard work wrote the
code.
Thanks for responding and glad you got it all to work satisfactorily.