Results 1 to 21 of 21

Extracting and transferring data across sheets

Threaded View

  1. #1
    Registered User
    Join Date
    06-18-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    13

    Extracting and transferring data across sheets

    Hi,
    I'm trying to write the code to get certain rows of data on one sheet to transfer to another sheet. The data sheet is denoted "Sheet1" and the compiled sheet is denoted "Sheet2". The compiled sheet has one row of input that includes values as well as 8 categories/classifications. If all of those 8 categories match an entry in the data sheet, then the entire row will be transferred to the compiled sheet. All of the data fields match up in each sheet; that is, the qualifications are in columns 19-26 in both. The rows to be transferred will include columns 2-26, and will go into the second sheet starting at row 3.
    I'm using nested looping structures to do this, and what I have seems as if it'll make sense, but when I go to run it, there is a type mismatch. The part that gets highlighted is at the very beginning: Private Sub Transfer()
    What am I doing wrong?

    -------------------------------------------------------

    Private Sub Transfer()
    
    ' Set variable types
    Dim Lastrow As Long
    Dim j As Integer
    Dim k As Integer
    Dim m As Integer
    Dim r As Integer
    Dim Check As Boolean
    
    'Find the last row
    Lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(x1Up)
    
    ' j is used to test the columns containing qualifications
    j = 19
    
    ' k is used for columns when transferring an entire row from Sheet1 to Sheet2
    k = 2
    
    ' m is used for rows when transferring an entire row from Sheet1 to Sheet2
    m = 3
    
    ' Loop through the rows in the data sheet (Sheet1)
    For r = 2 To Lastrow
    
        ' Loop through the qualification columns on both sheets, marking True or False if they match
        For j = 19 To j = 26
            If Sheets("Sheet1").Cells(r, j) = Sheets("Sheet2").Cells(2, j) Then
                j = j + 1
                Check = True
            Else
                Check = False
                End
                
        ' If the qualifications match, transfer the row
        If Check Is True Then
            For k = 2 To k = 26
                Sheets("Sheet2").Cells(m, k) = Sheets("Sheet1").Cells(r, k)
                k = k + 1
            m = m + 1
        End If
        
    End Sub
    Last edited by arlu1201; 06-18-2012 at 09:32 AM. Reason: Code tags. Please put them in future.

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