+ Reply to Thread
Results 1 to 2 of 2

Copy matching lines

Hybrid View

cbrd Copy matching lines 12-30-2005, 04:48 PM
cbrd I edited the post to be more... 12-31-2005, 10:42 AM
  1. #1
    Registered User
    Join Date
    12-30-2005
    Posts
    45

    Copy matching lines

    I have 2 workbooks, Ill call them wb1 and wb2.

    I have two worksheets, call them w1 and w2.
    On w1 I have on Row 1 my column titles like this:

    CUSTID | NAME | WORK DONE | DATE | PRICE | DONE? |

    On w2 I have in cell H10 a cell where a CUSTID will be typed. I also have on Row 17 column titles like this:
    (a thru e column) (f thru h) (I)

    WORK PERFORMED | DATE | PRICE |

    When a Customer ID is typed into cell H10 on w2, I want all rows with matching CUSTID's on w1 to be placed, starting on Row 18 on w2. I only need the info from column C, D, and E copied over.

    Anyone have any ideas?

    The sample .xls is here: http://www.ashleylandscaping.com/sample.xls

    or

    Im not too swift with VB but heres what I have so far:

    Private Sub Worksheet_Change(ByVal Target As Range)
            Dim GetSht As Worksheet, PutSht As Worksheet
            Dim rngCheck As Range, r As Range, H10 As Range
         
            Set GetSht = Sheets("Job History")
            Set PutSht = Sheets("Make - Invoice")
            Set r = PutSht.Range("a18")
            Set rngCheck = GetSht.Range("a1:a9999")
            
            For Each H10 In GetSht.Range("a:a")
            If rngCheck = H10 And rngCheck.Offset(0, 6) = "n" Then
            rngCheck.Offset(0, 2).Value = r.Offset(0, 1).Value
            rngCheck.Offset(0, 3).Value = r.Offset(0, 6).Value
            rngCheck.Offset(0, 4).Value = r.Offset(0, 9).Value
            End If
            Next H10
    End Sub

  2. #2
    Registered User
    Join Date
    12-30-2005
    Posts
    45
    I edited the post to be more desciptive, hope it helps.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1