CODEWITHSUNDEEP
vbaexcel
excel
excel

Reputation: 103

how to extract multiple column data in Excel VBA

I'm doing a macro which extracts data from certain columns from another file. I have encountered an error message of

Type mismatch

at line Set row = Columns("O,Q,W") I know this is not correct and I have tried with Union also but that is also not working.

Sub ImportDatafromotherworksheet()
Dim wkbCrntWorkBook As Workbook
Dim wkbSourceBook As Workbook
Dim rngSourceRange As Range
Dim rngDestination As Range
Dim row As Range
Dim row1 As Integer
Dim hello As Range
Dim hello1 As Range
Dim lastRow As Long, i As Long
Dim CopyRange As Range
Set wkbCrntWorkBook = ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count > 0 Then
        Workbooks.Open .SelectedItems(1)
        Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))

        With wkbSourceBook.Worksheets(1)
            lastRow = .Range("A" & .Rows.Count).End(xlUp).row

        For i = 4 To lastRow
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then
                If CopyRange Is Nothing Then
                    Set CopyRange = .Rows(i)
                Else
                Set CopyRange = Union(CopyRange, .Rows(i))
                Set row = Columns("O,Q,W")
                End If
            End If
        Next

        If Not CopyRange Is Nothing Then


            wkbCrntWorkBook.Activate
            Set rngDestination = Application.InputBox(Prompt:="Select destination cell", Title:="Select Destination", Default:="A1", Type:=8)
            row.Copy rngDestination
            rngDestination.CurrentRegion.EntirdoeColumn.AutoFit
            wkbSourceBook.Close False
            '~~> Change Sheet2 to relevant sheet name
        End If
        End With


    End If
End With
End Sub

Upvotes: 0

Views: 2365

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149305

You can't use Columns() like that. Also avoid the use of Row as a variable. It is a reserved word.

Change it to 

Set rw= .Range("O:O,Q:Q,W:W")

or like what @KKowalczyk suggested

Set rw = Union(.Columns(15), .Columns(17), .Columns(23))

Forgot to mention that please also qualify your cells object. Notice the dots before them? If you don't then they will address the Activesheet which may or may not be the sheet that you actually think it is.

Upvotes: 0

KKowalczyk
KKowalczyk

Reputation: 433

Use this:

Set MyRange = Union(Columns(1), Columns(4))

will catch several columns at once

Upvotes: 1

Related Questions