MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 97


Posted by Al on April 09, 2001 5:31 PM

How do I ?
Have multi tabs. Based on column one having text in it I want to copy only those rows to another spread sheet.
I have tried if using iftext nontext etc put it does not work.


Posted by Dave Hawley on April 09, 2001 10:57 PM

Hi AL

Try this

Sub OnlyText()
'Copy text only from Column A
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim CopyRange1 As Range
Dim CopyRange2 As Range
Dim AllRange As Range

With Sheets("sheet1")
On Error Resume Next

'Set to text formulas
Set CopyRange1 = _
.Columns(1).SpecialCells(xlCellTypeFormulas, 2)
'Copy entire rows
CopyRange1.EntireRow.Copy _
Destination:=Sheets("sheet2").Range("A1")

'Set to text constants
Set CopyRange2 = _
.Columns(1).SpecialCells(xlCellTypeConstants, 2)
'Copy entire rows
CopyRange2.EntireRow.Copy _
Destination:=Sheets("sheet2").Range("A65536").End(xlUp).Offset(1, 0)
End With

Set CopyRange1 = Nothing
Set CopyRange2 = Nothing
End Sub

Dave


OzGrid Business Applications

Posted by Charlotte Converse on April 10, 2001 8:58 AM


Why?

H E L P