Selective paste

[Gordon]

Hi..

I have a block of data A9:10000 - X9:10000. Is there any code that would
allow me to select any combination of rows in which the word 'aged' is found
and then to compact them into a new sheet? For example, if the word 'aged' is
only found on 130 random rows then those 130 are pasted into a new sheet as
one block of text.

Any help would be appreciated...

Thanks G

 

[ND Pard]

I'm using Excel 2007 and the following works for me (manually and/or via VBA
coding).

Use a filter to limit your data, copy the filtered data and paste it into
your new worksheet.

Good Luck.

 

[StumpedAgain]

This should do the trick. You can modify the "*aged*" if you want something
more specific.

Option Explicit
Sub Select_Aged()

Dim i As Long
Dim cell As Range
i = 0

For Each cell In ActiveSheet.UsedRange
If cell Like "*aged*" Then
cell.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1").Offset(i, 0)
i = i + 1
End If
Next cell

End Sub

 

[ramesh]

Please let us the code that would allow us to select combination of
rows/columns in which the word 'aged' or "independent" is found and
then to paste them into a new sheet with row/columnwise data also ?


ramesh

 

[StumpedAgain]

I'm sorry, that didn't make any sense. Are you wanting code where either
'aged' or 'independent' is found? If not, let me know. If so, try this:

Option Explicit
Sub Select_Aged()

Dim i As Long
Dim cell As Range
i = 0

For Each cell In ActiveSheet.UsedRange
If cell Like "*aged*" Or cell Like "independent" Then
cell.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1").Offset(i, 0)
i = i + 1
End If
Next cell

End Sub

 

[ramesh]

thanks for your code.

the code is copying from one sheet to another row wise.that is ok .

please let us know the code also for copying from one sheet to another
column wise in which word "aged" and or "independent" found .

ramesh

 

[StumpedAgain]

I have it copying over to a generic "Sheet2" so you'll probably have to
change that name in the line:

cell.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1").Offset(i, 0)

Also, if you want to change from rows to columns use the following:

Option Explicit
Sub Select_Aged()

Dim i As Long
Dim cell As Range
i = 0

For Each cell In ActiveSheet.UsedRange
If cell Like "*aged*" Or cell Like "independent" Then
cell.EntireColumn.Copy Destination:=Sheets("Sheet2").Range("A1").Offset(0, i)
i = i + 1
End If
Next cell

End Sub

 

[ramesh]

Thank you !!!!!!

ramesh