Excel sheet macro which will search a text and ask me to copy which colums

Sharath this question is in the wrong zone again.

Sorry.Is there any way to change after the Q is posted.?

you can request administrator by putiing an administrator question to change the zone

OK THANKS

by the way sharath, your question is not clear to me
>Say if i need to search sharath and i have 20 colums next to it.But i just need to copy the contents A,C,F,J.

Can you explain this a little more?

Say i have
Sharath in Colum C
Age in B
City in D
Country in A

I want a macro to ask me what all colums to copy.Say i select B and D
And another box which will ask me what to search and i say "Sharath"
So the results to a new page should be Age City Sharath

THX
Sharath

try this macro
Sub columncopy()
Dim str1 as Variant , searchvalue as String,s2row as Long
str1 = Split(InputBox("Columns to Copy"),";")  'Enter columns separated by ; e.g (B;D)
searchvalue = InputBox("Search Value")
s2row= 1
For i=1 to Sheet1.UsedRange.Rows.Count
    s2col = 1
    For i=1 to Sheet1.UsedRange.Columns.Count
          if Sheet1.Cells(i,C)= searchvalue then
                 For k =1 to UBound(str1)
                      Sheet2.Cells(s2row,k) = Sheet1.Cells(i,str1(k-1)
                 Next
                 s2row = s2row+1
          End if
    Next
Next

End Sub

I get this.

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

For control variable already in use
---------------------------
OK   Help  
---------------------------

sorry try this macro
Sub columncopy()
Dim str1 as Variant , searchvalue as String,s2row as Long
str1 = Split(InputBox("Columns to Copy"),";")  'Enter columns separated by ; e.g (B;D)
searchvalue = InputBox("Search Value")
s2row= 1
For i=1 to Sheet1.UsedRange.Rows.Count
    s2col = 1
    For j=1 to Sheet1.UsedRange.Columns.Count
          if Sheet1.Cells(i,C)= searchvalue then
                 For k =1 to UBound(str1)
                      Sheet2.Cells(s2row,k) = Sheet1.Cells(i,str1(k-1)
                 Next
                 s2row = s2row+1
          End if
    Next
Next

End Sub

Checking on this.Can you help me with this Q.Need it urgent

https://www.experts-exchange.com/?qid=22724761

I get this error.
---------------------------
Microsoft Visual Basic
---------------------------
Can't execute code in break mode
---------------------------
OK   Help  
---------------------------


I get this error in this point.

                      Sheet2.Cells(s2row,k) = Sheet1.Cells(i,str1(k-1)

Sub columncopy()
Dim str1 as Variant , searchvalue as String,s2row as Long
str1 = Split(InputBox("Columns to Copy"),";")  'Enter column nos separated by ; e.g (1;3)
searchvalue = InputBox("Search Value")
s2row= 1
For i=1 to Sheet1.UsedRange.Rows.Count
    s2col = 1
    For j=1 to Sheet1.UsedRange.Columns.Count
          if Sheet1.Cells(i,j)= searchvalue then
                 For k =1 to UBound(str1)
                      Sheet2.Cells(s2row,s2col) = Sheet1.Cells(i,Val(str1(k-1)))
                      s2col = s2col + 1
                 Next
                 s2row = s2row+1
          End if
    Next
Next

End Sub

did u try it

Checked but not working.No error.
Its asking me for the colums to search.I specify A,B,C
then it asks me text to search.I specify Sharath
But does not copy the colums to new sheet

Sub columncopy()
Dim str1 as Variant , searchvalue as String,s2row as Long
str1 = Split(InputBox("Columns to Copy"),",")  'Enter column nos separated by , e.g (1,3,)
searchvalue = InputBox("Search Value")
s2row= 1
For i=1 to Sheet1.UsedRange.Rows.Count
    s2col = 1
    For j=1 to Sheet1.UsedRange.Columns.Count
          if Sheet1.Cells(i,j)= searchvalue then
                 For k =1 to UBound(str1)
                      Sheet1.Columns(k).Copy Destination:= Sheet2.Columns(s2col)                  
                      s2col = s2col + 1
                 Next
                  Sheet1.Columns(j).Copy Destination:= Sheet2.Columns(s2col)
                 s2row = s2row+1
          End if
    Next
Next

End Sub

Enter Column Nos like this 1,2,3,
not A,B,C

The copy box is asking me to enter 2 times.Ebven when i enter i dont et the colums copied.

the first time you have to enter the column nos.
second time the search value

Ok got it,

When i type the colum 1,3 in first box and Sharath in 2nd box.
It only copies colum A(1) 2 times to sheet 2.

enter 1,3,

if possible upload your file in 2003 format.

File uploaded.

http://www.filepatio.com/3628

it is in 2007 format

Sorry
http://www.filepatio.com/3634

Use the following macro, type 2,3, and search value as Sharath and see the result
Sub columncopy()
Dim str1 As Variant, searchvalue As String, s2row As Long
str1 = Split(InputBox("Columns to Copy"), ",") 'Enter column nos separated by , e.g (1,3,)
searchvalue = InputBox("Search Value")
s2row = 1
For i = 1 To Sheet1.UsedRange.Rows.Count
    s2col = 1
    For j = 1 To Sheet1.UsedRange.Columns.Count
          If Sheet1.Cells(i, j) = searchvalue Then
                 For k = 1 To UBound(str1)
                      Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)
                      s2col = s2col + 1
                 Next
                  Sheet1.Columns(j).Copy Destination:=Sheet2.Columns(s2col)
                 s2row = s2row + 1
          End If
    Next
Next

End Sub

I get 1004 error.
Debug
  Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)

Is it case sensitive?

when you get the error can you tell me the values of s2col and str1(k-1) by typing the following in the immediate window

?s2col
?str1(k-1)

Sub columncopy()
Dim str1 As Variant, searchvalue As String, s2row As Long
str1 = Split(InputBox("Columns to Copy"), ",") 'Enter column nos separated by , e.g (1,3,)
searchvalue = InputBox("Search Value")
s2row = 1
For i = 1 To Sheet1.UsedRange.Rows.Count
    s2col = 1
    For j = 1 To Sheet1.UsedRange.Columns.Count
          If UCase(Sheet1.Cells(i, j)) = UCase(searchvalue) Then
                 For k = 1 To UBound(str1)
                      Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)
                      s2col = s2col + 1
                 Next
                  Sheet1.Columns(j).Copy Destination:=Sheet2.Columns(s2col)
                 s2row = s2row + 1
          End If
    Next
Next

End Sub

You mean to say
In the 1st box
?s2col
In the second box
?str1(k-1)
I get no results.If this is not the way.Please tell me.

I have tried the macro, it works fine, can you tell me what exactly are you entering in the input boxes

In the first box i entered A,C,
In the 2nd box Sharath

I have 3 colums in the excel.
name  Age  Sex
Sharath  29  M

you have to enter column nos like this 1,3,
not column letters

Ok in sheet 1 i have this.

Name       Age      Sex
Sharath      35      M
ramesh      23      M

When it asks me for the colum i mention 1,3,
When it asks me to search
Sharath
This is what i get in the sheet 2
Name       Sex      Name
Sharath      M      Sharath
ramesh      M      ramesh

Isnt that what you want?, if not what should be the output in this case

I should only have this in sheet 2

Name            Sex
Sharath           M
ramesh           M

Error.

Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)

Application -defined or object defined errors.

i hope you have entered column nos like this 1,3,
not column letters

I have even tried colum letters.
Debug
Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)

buddy i dont know whats going wrong here i have tried it here it works fine, can u tell me what exactly you typed