+ Reply to Thread
Results 1 to 6 of 6

Copy Multiple Rows Based on Cell Value

Hybrid View

phelbin Copy Multiple Rows Based on... 07-26-2016, 12:27 PM
Bishonen Re: Copy Multiple Rows Based... 07-26-2016, 12:42 PM
phelbin Re: Copy Multiple Rows Based... 07-26-2016, 12:46 PM
Bishonen Re: Copy Multiple Rows Based... 07-26-2016, 12:48 PM
phelbin Re: Copy Multiple Rows Based... 07-26-2016, 12:51 PM
phelbin Re: Copy Multiple Rows Based... 07-26-2016, 01:50 PM
  1. #1
    Forum Contributor
    Join Date
    02-18-2014
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    197

    Copy Multiple Rows Based on Cell Value

    I have a workbook with an approval page. The manager ticks the check boxes and then presses a button labeled "approve". What I need then is to copy all of the approved lines...the check box puts a TRUE/FALSE in column Z.

    The following code copies only the first line that returns TRUE in column Z. But I'd like to copy all lines with TRUE, and I can't figure out how to do it. Any ideas?


    
    Sub CopyRowWith()
        
    Last = Cells(Rows.Count, 7).End(xlUp).Row
        
        For i = Last To 1 Step -1
            If (Cells(i, "Z").Value) = True Then
                Cells(i, "A").EntireRow.Copy
            End If
        Next i
    
    
    End Sub

    Thanks!!

  2. #2
    Valued Forum Contributor
    Join Date
    03-17-2012
    Location
    Warsaw, Poland
    MS-Off Ver
    2007/2010
    Posts
    555

    Re: Copy Multiple Rows Based on Cell Value

    
    Sub CopyRowWith()
    
    dim copyRange as Range
        
    Last = Cells(Rows.Count, 7).End(xlUp).Row
        
        For i = Last To 1 Step -1
            If (Cells(i, "Z").Value) = True Then
                set copyRange = union(copyRange, Cells(i, "A").entirerow)
            End If
        Next i
    
    copyRange.copy
    
    End Sub
    If you think that my answer was helpful, please click on the "Add to this user's Reputation" button.

  3. #3
    Forum Contributor
    Join Date
    02-18-2014
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    197

    Re: Copy Multiple Rows Based on Cell Value

    Bishonen, that gives me a run-time error 5. Invalid procedure call or argument.

    It doesn't like the line Set copyRange = Union(copyRange, Cells(i, "A").EntireRow)

  4. #4
    Valued Forum Contributor
    Join Date
    03-17-2012
    Location
    Warsaw, Poland
    MS-Off Ver
    2007/2010
    Posts
    555

    Re: Copy Multiple Rows Based on Cell Value

    ye, sorry, didnt test it. You need to initiate copyRange apparently:

    
    Sub CopyRowWith()
    
    dim copyRange as Range
        
    Last = Cells(Rows.Count, 7).End(xlUp).Row
        
        For i = Last To 1 Step -1
            If (Cells(i, "Z").Value) = True Then
                if copyRange is nothing then
                    set copyRange = Cells(i,"A").entirerow
                else
                    set copyRange = union(copyRange, Cells(i, "A").entirerow)
                End if
            End If
        Next i
    
    copyRange.copy
    
    End Sub

  5. #5
    Forum Contributor
    Join Date
    02-18-2014
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    197

    Re: Copy Multiple Rows Based on Cell Value

    It works. Thanks so much!!

  6. #6
    Forum Contributor
    Join Date
    02-18-2014
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    197

    Re: Copy Multiple Rows Based on Cell Value

    One added question

    How would I insert this condition into the above code...if all cells in column Z are FALSE, then exit sub.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy and Paste rows to multiple worksheets based on a cell value.
    By gaidon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-22-2013, 12:43 PM
  2. [SOLVED] Insert Multiple Rows Based Off Number in Cell and Copy Data From Above New Rows
    By tstell1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2012, 04:15 PM
  3. Replies: 8
    Last Post: 09-18-2011, 05:36 AM
  4. copy rows based on multiple cell values
    By stevenwhite1968 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2011, 07:18 AM
  5. copy multiple rows and sort based on cell values
    By stevenwhite1968 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-18-2011, 03:12 AM
  6. macro to copy multiple rows based on cell entry
    By uncleslinky in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2011, 07:23 AM
  7. Replies: 11
    Last Post: 08-24-2009, 11:44 AM

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