VBA: HELP NEEDED. Importing only certain columns

[Ouble]

Hi guys,

I having trouble importing only the column a and b. I am importing excel file data (500 of them) into a single sheet and would only like to extract values from 2 columns. I am new to vba and got dumped by this job because our IT guy is in the hospital. My job is on the line

need all the help in the world...

Sub ImportFiles()
    Dim Fldr As String, FN As String
    Dim wsDst As Worksheet, rngDst As Range
     
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "cancelled by user"
            Exit Sub
        End If
        Fldr = .SelectedItems(1)
    End With
     
    
    Set wsDst = ThisWorkbook.Sheets("Sheet1")


    FN = Dir(Fldr & "\*.xls", vbNormal)
    Do While FN <> ""
        Workbooks.OpenText Filename:=Fldr & "\" & FN, Space:=False
        
        Set rngDst = Union(Range(Columns(1), Columns(2))).Select
        Set rngDst = wsDst.Range("A" & wsDst.Rows.Count).End(xlUp).Offset(2)
        ActiveSheet.UsedRange.Copy rngDst
        FN = Dir()
        ActiveWorkbook.Close False
    Loop


End Sub

Thank you very much.

Ouble

 

[Andrew Poulsom]

Why are you using the variable rngDst twice? I think you need:

ActiveSheet.UsedRange.Resize(, 2).Copy rngDst

 

[Ouble]

Hi Sir,

It works wonderfully! Thank you so much.

My boss is a very picky person to work with. He commented that he wants the following data to be placed in the columns to the right instead stacking right below each other. Additionally, he wants the filename of the imported data to be at the top of each data.

I have no idea how to place the data side by side in columns.

As for the filename, I managed to come up with this but the code isnt working.

cl value = file name


'put data in row 1 of active sheet
For i=0 To UBound(items)
cl.Offset(0,I+1(.Value = Items(i)
Next

Please help. Thank you for all that trouble.

Ouble