"I have multiple CSV files in the same folder. How can i merge all the CSV files into a single XLS file where all the CSV files are on their own separate sheet tab?"
Sub ImportCSVs() 'Author: Jerry Beaucaire 'Date: 8/16/2010 'Summary: Import all CSV files from a folder into separate sheets ' named for the CSV filenames
'Update: 2/8/2013 Macro replaces existing sheets if they already exist in master workbook
Dim fPath As String Dim fCSV As String Dim wbCSV As Workbook Dim wbMST As Workbook
Set wbMST = ThisWorkbook fPath = "C:\2013\" 'path to CSV files, include the final \ Application.ScreenUpdating = False 'speed up macro Application.DisplayAlerts = False 'no error messages, take default answers fCSV = Dir(fPath & "*.csv") 'start the CSV file listing
On Error Resume Next Do While Len(fCSV) > 0 Set wbCSV = Workbooks.Open(fPath & fCSV) 'open a CSV file wbMST.Sheets(ActiveSheet.Name).Delete 'delete sheet if it exists ActiveSheet.Move After:=wbMST.Sheets(wbMST.Sheets.Count) 'move new sheet into Mstr Columns.Autofit 'clean up display fCSV = Dir 'ready next CSV Loop
Application.ScreenUpdating = True Set wbCSV = Nothing End Sub