Copying data from all workbooks in folder into current Worksheet

[Grange2]

Greetings Everyone,

I'm looking to copy the first 2 columns from all the CSV files in a folder. All csv files have only 1 worksheet and they all have different names.
I'm looking to try and see if its possible to copy and paste the data so it overwrites what was previously copied and pasted with each iteration(new file opened).
The issue i come across is that it doesn't actually paste the new information into the sheet. (I understand that im overwriting the existing data but i plan to call a macro that will do something with the data before the next iteration.)
This is what i have so far:


Sub Copydatafolder()
Dim fName As Variant
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")

fName = Dir("Filedestination\*.csv")

While fName <> ""

Columns("A:B").Select
Selection.Copy

ws.Range("A1").PasteSpecial

fileName = Dir

Wend


I would appreciate your assistance with this

Thank you,

 

[Fluff]

You need to open the files like

   Dim fName As String, Pth As String
   Dim ws As Worksheet
   Dim Wbk As Workbook
   
   Set ws = ActiveWorkbook.Sheets("Sheet1")
   Pth = "C:\Mrexcel\"
   fName = Dir(Pth & "*.csv")
   
   Do While fName <> ""
      Set Wbk = Workbooks.Open(Pth & fName)
      Wbk.Sheets(1).Columns("A:B").Copy ws.Range("A1")
      Wbk.Close False
      FileName = Dir
   Loop

Change Pth to your actual file path

 

[Grange2]

Thank you!

That makes more sense! That works perfectly.

 

[Fluff]

You're welcome & thanks for the feedback.