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

[waptrick]

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?

 

[Zot]

The code below once run, it will ask you to select a folder. So, you are required to put all files you wanted to copy from into this folder before run.

The code will open each file in the folder, copy the first sheet in file into a newly added sheet. The sheet will be just numbered.

Sub LoopThroughFiles()

Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim SelectFolder As Long
Dim ws As Worksheet, wsData As Worksheet
Dim wb As Workbook, wbData As Workbook

Application.ScreenUpdating = False

SelectFolder = Application.FileDialog(msoFileDialogFolderPicker).Show
If Not SelectFolder = 0 Then
    strPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
Else
    End
End If

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(strPath)

' Define current workbook (RDI) as wb
Set wb = ActiveWorkbook

For Each oFile In oFolder.Files
    ' Open each workbook in the folder and define as wbData
    Set wbData = Workbooks.Open(Filename:=oFile, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    ' Define Sheet1 of opened workbook as wsData
    Set wsData = wbData.Sheets(1)

    ' Add new sheet to RDI workbook and defined as ws.
    wb.Sheets.Add After:=wb.Sheets(wb.Sheets.Count)
    Set ws = wb.ActiveSheet
    ' Copy sheet into RDI
    wsData.Cells.Copy ws.Range("A1")
    'Close wbData without saving
    wbData.Close False
Next oFile

Application.ScreenUpdating = True

End Sub

 

[waptrick]

Thank you Zot! That worked great!

I wanted to slightly modify the code so that the output was formatted in a specific way. I'll explain:

- I am analyzing travel and leisure trends across airlines, tour operators, cruises and OTAs, and each are grouped according to a geographic region (eg, US, UK, FR etc)
- Each downloaded CSV file represents a search term from Google Trends
- For example, I would download data for the search terms Flight, Delta Air Lines, American Airlines in the US, and each represents a separate CSV file download.
- I want to modify the code so that all search terms for say USAairlines shows up in one sheet

I have attached an image example of an excel file explaining what I mean, perhaps you may know of a way to do so?

I tried to use Python to extract data from Google Trends using Pytrends, but the data from Pytrends doesn't for some reason match the manual CSV download.

 

[Zot]

This is different task altogether. I think you should open another thread. You need to state:

How each imported sheet looks like?
How you wanted the end result like?