CODEWITHSUNDEEP
excelvba
Waptrick
Waptrick

Reputation: 3

Combine multiple workbooks each with one sheet into one workbook with multiple sheets

I have multiple CSV files, each with one worksheet.
The workbooks are saved under a specific name.
All the workbooks have the same format.

I have a separate workbook called RDI raw data.xlsm
I would like to copy all data from the workbooks into the RDI raw data file.
Each workbook needs to be a separate sheet in the RDI raw data file.

Upvotes: 0

Views: 424

Answers (2)

ASH
ASH

Reputation: 20302

If you want to learn VBA, there are several ways you can achieve your goal, and get an education too. If you want a slick alternative which requires no coding, and achieves the same result, consider using this Excel AddIn.

https://www.rondebruin.nl/win/addins/rdbmerge.htm

It will do what you want, and it will do a whole bunch of other things as well, all with no coding, whatsoever!!

Upvotes: 0

guest44
guest44

Reputation: 26

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

Upvotes: 1

Related Questions