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

Thanks:  0
Likes:  0

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

1. ## 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. ## 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. ## Re: Macro for copying cells with specific value in them to another worksheet

Originally Posted by Krishnakumar
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. ## 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. ## Re: Macro for copying cells with specific value in them to another worksheet

Originally Posted by Krishnakumar
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. ## 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

## User Tag List

#### Posting Permissions

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