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

Hi,

I need a macro which will search for a excel sheet content and ask me which all colums it needs to cop.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.How do i do it.

regards
Sharath
LVL 11
bsharathAsked:
Who is Participating?
 
Hitesh ManglaniConnect With a Mentor Commented:
Here you go
Sub columncopy()
Dim str1 As Variant, searchvalue As String, s2row As Long, donotcopy As Boolean
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
                      If Val(str1(k - 1)) = j Then
                          donotcopy = True
                      End If
                 Next
                If Not donotcopy Then
                  Sheet1.Columns(j).Copy Destination:=Sheet2.Columns(s2col)
                  Exit Sub
                End If
                  Exit Sub
               
          End If
    Next
Next

End Sub


0
 
Hitesh ManglaniCommented:
Sharath this question is in the wrong zone again.
0
 
bsharathAuthor Commented:
Sorry.Is there any way to change after the Q is posted.?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Hitesh ManglaniCommented:
you can request administrator by putiing an administrator question to change the zone
0
 
bsharathAuthor Commented:
OK THANKS
0
 
Hitesh ManglaniCommented:
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?

0
 
bsharathAuthor Commented:
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
0
 
Hitesh ManglaniCommented:
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
0
 
bsharathAuthor Commented:
I get this.

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

For control variable already in use
---------------------------
OK   Help  
---------------------------
0
 
Hitesh ManglaniCommented:
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
0
 
bsharathAuthor Commented:
Checking on this.Can you help me with this Q.Need it urgent

http://www.experts-exchange.com/?qid=22724761
0
 
bsharathAuthor Commented:
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)
0
 
Hitesh ManglaniCommented:
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
0
 
Hitesh ManglaniCommented:
did u try it
0
 
bsharathAuthor Commented:
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
0
 
Hitesh ManglaniCommented:
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
0
 
Hitesh ManglaniCommented:
Enter Column Nos like this 1,2,3,
not A,B,C
0
 
bsharathAuthor Commented:
The copy box is asking me to enter 2 times.Ebven when i enter i dont et the colums copied.
0
 
Hitesh ManglaniCommented:
the first time you have to enter the column nos.
second time the search value
0
 
bsharathAuthor Commented:
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.
0
 
Hitesh ManglaniCommented:
enter 1,3,
0
 
Hitesh ManglaniCommented:
if possible upload your file in 2003 format.
0
 
bsharathAuthor Commented:
0
 
Hitesh ManglaniCommented:
it is in 2007 format
0
 
bsharathAuthor Commented:
0
 
Hitesh ManglaniCommented:
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

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

Is it case sensitive?
0
 
Hitesh ManglaniCommented:
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
0
 
bsharathAuthor Commented:
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.
0
 
Hitesh ManglaniCommented:
I have tried the macro, it works fine, can you tell me what exactly are you entering in the input boxes
0
 
bsharathAuthor Commented:
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
0
 
Hitesh ManglaniCommented:
you have to enter column nos like this 1,3,
not column letters
0
 
bsharathAuthor Commented:
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

0
 
Hitesh ManglaniCommented:
Isnt that what you want?, if not what should be the output in this case
0
 
bsharathAuthor Commented:
I should only have this in sheet 2

Name            Sex
Sharath           M
ramesh           M
0
 
bsharathAuthor Commented:
Error.

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

Application -defined or object defined errors.
0
 
Hitesh ManglaniCommented:
i hope you have entered column nos like this 1,3,
not column letters

0
 
bsharathAuthor Commented:
I have even tried colum letters.
Debug
Sheet1.Columns(Val(str1(k - 1))).Copy Destination:=Sheet2.Columns(s2col)
0
 
Hitesh ManglaniCommented:
buddy i dont know whats going wrong here i have tried it here it works fine, can u tell me what exactly you typed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.