Results 1 to 6 of 6

Macro for copying cells with specific value in them to another worksheet

This is a discussion on Macro for copying cells with specific value in them to another worksheet within the Excel Questions forums, part of the Question Forums category; I am trying to copy a row of cells from one worksheet to another worksheet if the value of the ...

  1. #1
    New Member
    Join Date
    Aug 2008
    Posts
    19

    Default Macro for copying cells with specific value in them to another worksheet

    I am trying to copy a row of cells from one worksheet to another worksheet if the value of the first cell in the row is a specific letter/number. Does anyone know the macro code to do this?

  2. #2

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Macro for copying cells with specific value in them to another worksheet

    Hi,

    May be..

    Code:
    Sub kTest()
    Dim a, v, i As Long, w()
    
    a = Range("A1").CurrentRegion.Resize(, 1)
    ReDim w(1 To UBound(a, 1), 1 To 1)
    For Each v In a
        If UCase(Left$(v, 1)) = "A" Then 'if the first letter is A
            i = i + 1
            w(i, 1) = v
        End If
    Next
    With Sheets("Sheet2").Range("a1")
        .Resize(i).Value = w
    End With
    End Sub

  3. #3
    New Member
    Join Date
    Aug 2008
    Posts
    19

    Default Re: Macro for copying cells with specific value in them to another worksheet

    Quote Originally Posted by Krishnakumar View Post
    Hi,

    May be..

    Code:
    Sub kTest()
    Dim a, v, i As Long, w()
    
    a = Range("A1").CurrentRegion.Resize(, 1)
    ReDim w(1 To UBound(a, 1), 1 To 1)
    For Each v In a
        If UCase(Left$(v, 1)) = "A" Then 'if the first letter is A
            i = i + 1
            w(i, 1) = v
        End If
    Next
    With Sheets("Sheet2").Range("a1")
        .Resize(i).Value = w
    End With
    End Sub

    Worked nicely for copying over the individual letter (In your example, "A"), but didn't copy over the entire row of information


    I will provide an example (screen shot wasn't working at the moment)...
    Worksheet 1

    a Train 25 mph
    b car 5 mph
    c boat 15 mph
    a train 28 mph
    a train 45 mph
    a train 12 mph
    c boat 2 mph


    Worksheet 2

    a Train 25 mph
    a train 28 mph
    a train 45 mph
    a train 12 mph

  4. #4

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Macro for copying cells with specific value in them to another worksheet

    Hi,

    Code:
    Sub kTest()
    Dim a, i As Long, w(), n As Long, c As Long
    
    a = Range("A1").CurrentRegion.Resize(, 3) '3 columns
    ReDim w(1 To UBound(a, 1), 1 To 3)
    For i = 1 To UBound(a, 1)
        If UCase(Left$(a(i, 1), 1)) = "A" Then 'if the first letter is A
            n = n + 1
            For c = 1 To 3: w(n, c) = a(i, c): Next
        End If
    Next
    With Sheets("Sheet2").Range("a1")
        .Resize(n, 3).Value = w
    End With
    End Sub
    HTH

  5. #5
    New Member
    Join Date
    Aug 2008
    Posts
    19

    Default Re: Macro for copying cells with specific value in them to another worksheet

    Quote Originally Posted by Krishnakumar View Post
    Hi,

    Code:
    Sub kTest()
    Dim a, i As Long, w(), n As Long, c As Long
    
    a = Range("A1").CurrentRegion.Resize(, 3) '3 columns
    ReDim w(1 To UBound(a, 1), 1 To 3)
    For i = 1 To UBound(a, 1)
        If UCase(Left$(a(i, 1), 1)) = "A" Then 'if the first letter is A
            n = n + 1
            For c = 1 To 3: w(n, c) = a(i, c): Next
        End If
    Next
    With Sheets("Sheet2").Range("a1")
        .Resize(n, 3).Value = w
    End With
    End Sub
    HTH

    for some reason I get an error message from the following code

    .Resize(n, 3).Value = w



    Also, if I want to expand the selection that is searched for the corresponding values to 7 columns and 200 rows, how do I do that in your code

  6. #6

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615

    Default Re: Macro for copying cells with specific value in them to another worksheet

    Hi,

    Try,

    Code:
    Sub kTest()
    Dim a, i As Long, w(), n As Long, c As Long
    
    a = Range("A1").CurrentRegion.Resize(, 7) '7 columns
    ReDim w(1 To UBound(a, 1), 1 To 7)
    For i = 1 To UBound(a, 1)
        If UCase(Left$(a(i, 1), 1)) = "K" Then 'if the first letter is A
            n = n + 1
            For c = 1 To 7: w(n, c) = a(i, c): Next
        End If
    Next
    If n > 0 Then
        With Sheets("Sheet2").Range("a1")
            .Resize(n, 7).Value = w
        End With
    Else
        MsgBox "No record found", vbInformation
    End If
    End Sub
    HTH

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com