Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective paste
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective paste
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. "Gordon" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective paste
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 -- -SA "Gordon" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective paste
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 *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective paste
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 -- -SA "ramesh" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective paste
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 *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective paste
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 -- -SA "ramesh" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective paste
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selective on Name | Excel Discussion (Misc queries) | |||
Selective calculation | Excel Worksheet Functions | |||
SELECTIVE DELETE | Excel Programming | |||
Selective Data | Excel Programming | |||
copy / paste selective rows | Excel Discussion (Misc queries) |