I'm new to VBA and I am trying to do the following:

1. I have multiple CSV files, each with one worksheet and the workbooks are saved under a specific name.
2. The format of all the workbooks are the same.

I would like to write a VBA code which would do the following in a separate workbook called RDI raw data.xlsm

• In RDI raw data

• Copy all data from the workbooks into the RDI raw data file but each workbook needs to be a separate sheet in the RDI raw data file

Could someone please help with this?

CodePudding user response：

Place csv files in a folder called Import locally one sub folder of where you saved your master RDI file. The macro will copies the first sheet in the csv file and place after the first sheet in your master.

Sub cmdImportCSV()

'import multiple sheets in data folder'
Dim wb As Workbook
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim myPath As String
Dim strFilename As String
Dim ws As Worksheet

'skip screen updating and alerts'
Application.ScreenUpdating = False
Application.DisplayAlerts = False


'set path to the folder that contains the worksheets to Import folder'
myPath = ThisWorkbook.Path & "\Import\"

'set import destination to current workbook'
Set wb = ThisWorkbook

'the first file in the source folder'
strFilename = Dir(myPath)

'Speed up importing by not recalculating during loop'
Application.Calculation = xlCalculationManual

'start a loop - import all files in directory'
Do Until strFilename = ""
    'set workbook source'
    Set wbSource = Workbooks.Open(Filename:=myPath & "\" & strFilename)
    'set the worksheet source to copy from'
    Set wsSource = wbSource.Worksheets(1)
    'set where the copy is going to'
    wsSource.Copy after:=wb.Worksheets(1)
    'close the current source workbook'
    wbSource.Close
    'returns the next source workbook'
    strFilename = Dir()
Loop

'Reactivate Automatic calculations'
Application.Calculation = xlCalculationAutomatic

'Reactivate - show screen updated and if errors'
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub