If Then macro with text

[kevin026]

Hi,

I need help with code that will search a list for cells with text in A1:A5
in sheet 2 and 3, and any text that exists will be listed in a column in
sheet 1.

what should I do?

thanks!

 

[Rick Rothstein \(MVP - VB\)]

Something like this maybe...

Sub CopyDataToSheet1()
Dim R As Long
Dim C As Range
Dim SH As Variant
R = 2 'start row in Sheet1
For Each SH In Array(Worksheets("Sheet2"), Worksheets("Sheet3"))
For Each C In SH.Range("A1:A5")
If C.Value <> "" Then
Worksheets("Sheet1").Cells(R, "A").Value = C.Value
R = R + 1
End If
Next
Next
End Sub

Rick

 

[Don Guillett]

Try this simple macro
Sub getinfofromshts()
For i = 2 To 3
sh = Sheets(i).Name
With Sheets(1)
For Each c In Sheets(sh).Range("a1:a8")
lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
If Not IsNumeric(c) Then c.Copy .Cells(lr, 1)
Next c
End With
Next i
End Sub