[Excel Macros] Creating Macros to search and copy columns

[TTF]

I have a spreadsheet that has many columns. I want to search the titles of the columns and copy all the content in the column that satifies the search criterion and paste it in different worksheet. Copying these columns manually is very time consuming and I wanted to create Macro that automate searching (based on their column title), copying (all contents in the column) and pasting (to a separate worksheet).

Any help is appreciated.

Thanks,

<TTF>

[manoshailu]

hi,

Try the below code.

Sub copycolumns()

'
' copycolumns Macro
' Variable Declaration
'    
    
    Dim strColName As String
    Dim intRng As Integer
    Dim i as Integer 
    Dim strVal As String
    
    intRng = InputBox("Enter the No. of Columns?", "No. of columns")  'To get the No. of Columns Available to Search
    strColName = InputBox("Enter the Column Name to Copy?", "Column Name") 'To Get the Column Name to Search 
    strSheetName = InputBox("Enter the Sheet Name to Paste?", "Sheet Name")'To get the Sheet Name to paste the content 

    For i = 1 To intRng
       'Store the Cell Value
        strVal = Cells(1, i)
        'Check the Value with the User given column name
        If UCase(strVal) = UCase(strColName) Then
'Select and Copy
            Cells(1, i).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy

'Select and Paste
            Sheets(strSheetName).Select
            Range("A1").Select
            ActiveSheet.Paste

        End If
    Next
End Sub

Shailaja:)

I have a spreadsheet that has many columns. I want to search the titles of the columns and copy all the content in the column that satifies the search criterion and paste it in different worksheet. Copying these columns manually is very time consuming and I wanted to create Macro that automate searching (based on their column title), copying (all contents in the column) and pasting (to a separate worksheet).

Any help is appreciated.

Thanks,

<TTF>

[TTF]

Thanks Shailaja,
the code works to copy and paste a single column. is there a way to make a multiple selection (columns) and paste them in a separate worksheet.

thanks,
TTF

[manoshailu]

hi

Try this select no. of columns from one sheet to another.

Sub copycolumns()

'
' copycolumns Macro
' Variable Declaration
'        

    Dim strColRng As String
    Dim strSheetName As String
    Dim intNoofCols As Integer
    Dim strColName() As String
    Dim strCurSheetName As String
    
    'To get the No. of Columns Available to Search
    intRng = InputBox("Enter the No. of Columns?", "No. of columns")  

    'To get the No. of Columns to copy and paste
    intNoofCols = InputBox("Enter the No. of Columns to Copy and Paste?", "Copy and Paste")

    'To set size of the Array
    ReDim Preserve strColName(intNoofCols)

    For i = 0 To intNoofCols - 1
        'To Get the Column Name to Search       
        strColName(i) = InputBox("Enter the Column Name to Copy?", "Column Name")
    Next

   'To get the Sheet Name to paste the content  
    strSheetName = InputBox("Enter the Sheet Name to Paste?", "Sheet Name")

    'To store the Current Sheet Name where to copy  

    strCurSheetName = ActiveSheet.Name

    For j = 0 To intNoofCols - 1 'To get the Column Names from the Array

          For i = 1 To intRng
           
                'To Select the Sheet which column to copy
                Sheets(strCurSheetName).Select  
                
                  'Store the Cell Value     
                strVal = Cells(1, i)

                'Check the Value with the User given column name
                If UCase(strVal) = UCase(Trim(strColName(j))) Then
            
                        'Select and Copy            
                         Cells(1, i).Select
                         Range(Selection, Selection.End(xlDown)).Select
                         Selection.Copy

                        'Select and Paste            
                        Sheets(strSheetName).Select
                        Cells(1, j + 1).Select
                        Range(Selection, Selection.End(xlDown)).Select
                        ActiveSheet.Paste

                 End If
          Next
     Next
End Sub

Shailaja :)

Thanks Shailaja,
the code works to copy and paste a single column. is there a way to make a multiple selection (columns) and paste them in a separate worksheet.

thanks,
TTF

[suman.banerjee]

Thanks Shailaja,

This code is working fine now my question is can we do change the name of the column and paste it in different sheet instead of pasting the same column name??

Appreciate your help!

Regards,
SM

[manoshailu]

hi SM,

I didn't get u. Can u explain thru an example.

Shailaja

[naresh73]

That was an awesome work Sailaja, Thanks a lot..
However what Suman was looking is : Instead of typing the columns names in the pop up If he could paste them in som edifferent Work Sheet before he cud run the code. So that code will pick all the columns names that need to be copied from that particular range.