search, copy and pastClosed

Question

Hello, 

Im quite new to macro in excel. Now i hav 1 project where i need to search for a certain part of the word in a sentence in a cell in a column. when the result is found, i want it to copy the whole column where the word is and paste the copied column to next column... 
Can any1 help me? thanks...

          col1           col2          col3           col 4            col5
row1
row2
row3  tx_tm        am_pm      tm_tt          gg_tx         yt_ab
row4     1              2             2                4                 5
row5     1              2             2                4                 5
row6     1              2             2                4                 5
row7     1              2             2                4                 5
row8     1              2             2                4                 5


lets consider the above example:
i need to find the word that contain "tx" in the heading which is in the row 3, but there are lots of column that contain "tx", copy the entire column (in this case, col1,col4) and paste them next to the original column.
Configuration: Windows 2003 / Internet Explorer 7.0

venkat1926 · Answer

the data is like this form A1  onwards

				
				
tx_tm	am_pm	tm_tt	gg_tx	yt_ab
1	2	2	4	5
1	2	2	4	5
1	2	2	4	5
1	2	2	4	5
1	2	2	4	5

try the macro test below 

Sub test()
Dim x As String, findbeg As Range, findend As Range, j As Integer, lastecell As Range
Dim m As Integer, n As Integer, lastcell As Range
Worksheets("sheet1").Activate
x = InputBox("type what string you want in yur case tx")
Set lastcell = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell)
For j = 1 To lastcell.Column
With Columns(j)
Set findbeg = .Cells.Find(what:=x, lookat:=xlPart)
If findbeg Is Nothing Then GoTo Nextj
m = findbeg.Column
GoTo nextstep
End With
Nextj:
Next j
nextstep:
For j = lastcell.Column To 1 Step -1
With Columns(j)
Set findend = .Cells.Find(what:=x, lookat:=xlPart)
If findend Is Nothing Then GoTo nextj1
n = findend.Column
GoTo nextstep1
End With
nextj1:
Next j
nextstep1:
Range(Cells(1, findbeg.Column), Cells(1, findend.Column)).EntireColumn.Copy
'Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial
Cells(1, lastcell.Column + 1).PasteSpecial

Application.CutCopyMode = False
End Sub


Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
Application.CutCopyMode = False
End Sub


if you want to retest run "undo" first and again run "test"

limmie · Answer

hi venkat1926, thanks a lot for your help...
the coding works but the thing is now, I just want it to look for the (from my example) row 3 only, for the word tx, then copy the entire column and paste it next to the column that contain tx, not at the end of everything.
would you pls help me? thanks a lot....

venkat1926 · Answer

perhaps mind not working efficiently. ;your question no clear. give an example 
original data 
what it should like

Search, copy and past

3 responses

Similar discussions

Subscribe To Our Newsletter!