RSS

[Excel Macros] Creating Macros to search and copy columns

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums

Views: 3620 | Replies: 3 | Thread Tools Display Modes

•

Join Date: Jul 2008

Posts: 2

Reputation: TTF is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

TTF

Offline

Newbie Poster

[Excel Macros] Creating Macros to search and copy columns

Jul 9th, 2008

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>

•

Join Date: Jun 2007

Posts: 78

Reputation: manoshailu is an unknown quantity at this point

Rep Power: 2

Solved Threads: 6

manoshailu manoshailu is offline

Offline

Junior Poster in Training

Re: [Excel Macros] Creating Macros to search and copy columns

Jul 18th, 2008

hi,

Try the below code.

Help with Code Tags

(Toggle Plain Text)

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

•

Originally Posted by 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>

•

Join Date: Jul 2008

Posts: 2

Reputation: TTF is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

TTF

Offline

Newbie Poster

Re: [Excel Macros] Creating Macros to search and copy columns

Jul 23rd, 2008

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

•

Join Date: Jun 2007

Posts: 78

Reputation: manoshailu is an unknown quantity at this point

Rep Power: 2

Solved Threads: 6

manoshailu manoshailu is offline

Offline

Junior Poster in Training

Re: [Excel Macros] Creating Macros to search and copy columns

Jul 24th, 2008

hi

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

Help with Code Tags

(Toggle Plain Text)

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

•

Originally Posted by 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