Copy rows from different worksheets

[cbrd]

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

[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


"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
>
>

[Toppers]

Sorry a minor typo:

Should be (although original works!):

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

"Toppers" wrote:

> 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
> >
> >

[cbrd]

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

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

I appreciate the help, now I can sit and stare at the screen for 5 hours (and still wont get it) to try and figure out what you did and 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.


--
Regards

Roger Govier


"cbrd" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks! thats almost it. The only problem is that when I select a few
> of
> the names (smith and jones for example), it is inserting rows that
> dont
> contain their Cust ID #. I reuploaded the .xls so you can see what I
> mean.
>
> http://www.ashleylandscaping.com/sample.xls
>
> I appreciate the help, now I can sit and stare at the screen for 5
> hours (and still wont get it) to try and figure out what you did and
> how it works.
>
>
> --
> cbrd
> ------------------------------------------------------------------------
> cbrd's Profile:
> http://www.excelforum.com/member.php...o&userid=30009
> View this thread:
> http://www.excelforum.com/showthread...hreadid=499618
>

[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" wrote:

>
> 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.
>
>
> --
> cbrd
> ------------------------------------------------------------------------
> cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
> View this thread: http://www.excelforum.com/showthread...hreadid=499618
>
>

[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.

--
Regards

Roger Govier


"cbrd" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks you two ...works great.
>
>
> --
> cbrd
> ------------------------------------------------------------------------
> cbrd's Profile:
> http://www.excelforum.com/member.php...o&userid=30009
> View this thread:
> http://www.excelforum.com/showthread...hreadid=499618
>