Copy rows from different worksheets
 

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

--
cbr
-----------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...fo&userid=3000
View this thread: http://www.excelforum.com/showthread.php?threadid=49961

Copy rows from different worksheets
 

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

Copy rows from different worksheets
 

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

Copy rows from different worksheets
 

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

--
cbr
-----------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...fo&userid=3000
View this thread: http://www.excelforum.com/showthread.php?threadid=49961

Copy rows from different worksheets
 

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

Copy rows from different worksheets
 

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" wrote in
message ...

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

Copy rows from different worksheets
 

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

Copy rows from different worksheets
 

Thanks you two ...works great

--
cbr
-----------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...fo&userid=3000
View this thread: http://www.excelforum.com/showthread.php?threadid=49961

Copy rows from different worksheets
 

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" wrote in
message ...

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