Results 1 to 3 of 3

type mismatch while trying to filter and copy rows and paste to another worksheet

Threaded View

  1. #1
    Registered User
    Join Date
    10-06-2011
    Location
    Salt Lake City, UT
    MS-Off Ver
    Excel 2010
    Posts
    78

    type mismatch while trying to filter and copy rows and paste to another worksheet

    I am trying to copy rows from a sheet to another sheet while filtering the rows by several criteria. I want to tell the program what column to look in on the first sheet and what value I want in that column. I use the following code to reference a module with a sub called "Copyrowsandpaste":

    Copyrowsandpaste Worksheets("wobid"), Worksheets("res"), "S", "Completed"
    where S is the column and Completed is the desired value. The module functions when I have only one criteria:

    Sub Copyrowsandpaste(fromsheet As Worksheet, tosheet As Worksheet, Optional col1, Optional crit1)
    
    Dim i As Long
    Dim LastRow As Long
    fromsheet.Activate
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range(col1 & i) = crit1 _
            Then
            Range(col1 & i).EntireRow.Copy tosheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    Next i
    End Sub
    But when I try to add other criteria, I get an error:

    Sub Copyrowsandpaste(fromsheet As Worksheet, tosheet As Worksheet, Optional col1, Optional crit1, _
    Optional col2, Optional crit2, Optional col3, Optional crit3, Optional col4, Optional crit4, _
    Optional col5, Optional crit5)
    
    Dim i As Long
    Dim LastRow As Long
    fromsheet.Activate
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range(col1 & i) = crit1 _
        And Range(col2 & i) = crit2 _
        And Range(col3 & i) = crit3 _
        And Range(col4 & i) = crit4 _
        And Range(col5 & i) = crit5 _
        Then
            Range(col1 & i).EntireRow.Copy tosheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    Next i
    End Sub
    I get a type mismatch error. Any ideas how can I fix this error?


    Thank you!
    Attached Files Attached Files
    Last edited by perducci; 09-24-2012 at 01:51 PM. Reason: Clarification

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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