Copy columns from one sheet to a new sheet

Question

I want to copy multiple columns from worksheet named "Data" and create a new sheet to paste those columns.

I get

run time error "438" object doesn't support this property or method.

on the line

For Each xlcell In .myRng.Cells ' look through each cell in your header

My complete code.

Sub extractcols()

    Dim myCollection    As Collection
    Dim myIterator      As Variant
    Dim myRng           As Range
    Dim xlcell          As Variant
    Dim ws              As Worksheet
    Dim wsh             As Worksheet
    Dim colCounter      As Integer
    
    Dim lCol As Long, i As Long

    Set ws = ActiveWorkbook.Worksheets("Data")
    Set myCollection = New Collection

    'Create a collection of header names to search through
    myCollection.Add ("1")
    myCollection.Add ("2")
    myCollection.Add ("3")
    
    'Add worksheet
    With ThisWorkbook
 
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Main"
 
        Set wsh = ActiveWorkbook.Worksheets("Main")
    
        'Where to search, this is the header
        With ThisWorkbook.Sheets("Data")
            lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    
            colCounter = 0
            For Each xlcell In .myRng.Cells ' look through each cell in your header
                For Each myIterator In myCollection ' look in each item in the collection
                    If myIterator = xlcell.Value Then ' when the header matches what you are looking for
                        colCounter = colCounter + 1 ' creating a column index for the new workbook
                        ws.Columns(xlcell.Column).Copy
                        wsh.Columns(colCounter).Select
                        wsh.Paste
                    End If
                Next
            Next
        End With
    End With
End Sub

I defined the column headers or column names that I want to copy from sheet "Data" as myCollection.Add.

I want to copy these specified columns from the sheet named "Data" then create a new sheet named "Main" and paste these columns in the new sheet.

Remove the . in front of myRng, and then make sure to Set myRng = ... before looping. — BigBen
myRng has not been created. You probably need Set myRng = .range("A1", .cells(1, lCol)) And then do what BigBen suggested, using: For Each xlcell In myRng.Cells. And for copying you should not select anything: ws.Columns(xlcell.Column).Copy wsh.Columns(colCounter). On one row... — FaneDuru
@FaneDuru thanks it's perfect now thanks for your guidance please paste your answer for acceptance — Sandy

FaneDuru FaneDuru · Accepted Answer · 2020-12-02T07:51:11

Firstly, myRng is only declared and not defined. Try setting it in the next way:

Set myRng = .range("A1", .cells(1, lCol))

Then, use it like:

For Each xlcell In myRng.Cells 'without dot (.) in front of it

No need of any selection to copy the range. In this situation, it only consumes Excel resources. In fact, this part of your code should look like:

'your existing code
'...
    lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Set myRng = .range("A1", .cells(1, lCol))    
    colCounter = 0
    For Each xlcell In myRng.Cells ' look through each cell in your header
        For Each myIterator In myCollection ' look in each item in the collection
            If myIterator = xlcell.Value Then ' when the header matches what you are looking for
                colCounter = colCounter + 1 ' creating a column index for the new workbook
                ws.Columns(xlcell.Column).Copy wsh.Columns(colCounter) 'if you want pasting in the incremented column number...
            End If
        Next
    Next
'...
'your existing code...

Copy columns from one sheet to a new sheet

1 Answers