I have this macro which copies all of the 1st sheets from any excel
files in my c:\ directory.

Option Explicit
Private root As String
Sub ListFiles()
Dim sFileName As String
Dim index As Long
Dim ws As Worksheet

Set ws = ActiveSheet

root = "C:\"
ws.Range("A:A").Clear

sFileName = Dir(root & "*.XLS")
Do While sFileName < ""
index = index + 1
ws.Cells(index, 1) = sFileName
CopySheets sFileName
ActiveSheet.Name = "Sheet_" & index ''NEW LINE
sFileName = Dir()
Loop

MsgBox ("Completed Copying")
End Sub
Sub CopySheets(sFileName As String)
Dim wb As Workbook
Set wb = Workbooks.Open(root & sFileName)
ActiveWindow.WindowState = xlNormal
wb.Sheets(1).Copy Befo=ThisWorkbook.Sheets(1)
wb.Close False
End Sub

I now want a seperate macro that can delete all of these new sheets.

Somebody has told me to use:-
Application.DisplayAlerts=False
worksheets(n).Delete
Application.DisplayAlerts=True

but I can't seem to just paste this into a new macro.

Any help would be greatly appreciated!


---
Message posted from http://www.ExcelForum.com/

No, you would have to introduce the code into a macro similar to the one you
have. The code provided just shows how to delete a sheet (sheet n in the
tab order) without prompt. You still need code like you have to open the
workbooks and identify which workbooks to delete the sheet in.

--
Regards,
Tom Ogilvy

ianripping wrote in message
...
I have this macro which copies all of the 1st sheets from any excel
files in my c:\ directory.

Option Explicit
Private root As String
Sub ListFiles()
Dim sFileName As String
Dim index As Long
Dim ws As Worksheet

Set ws = ActiveSheet

root = "C:\"
ws.Range("A:A").Clear

sFileName = Dir(root & "*.XLS")
Do While sFileName < ""
index = index + 1
ws.Cells(index, 1) = sFileName
CopySheets sFileName
ActiveSheet.Name = "Sheet_" & index ''NEW LINE
sFileName = Dir()
Loop

MsgBox ("Completed Copying")
End Sub
Sub CopySheets(sFileName As String)
Dim wb As Workbook
Set wb = Workbooks.Open(root & sFileName)
ActiveWindow.WindowState = xlNormal
wb.Sheets(1).Copy Befo=ThisWorkbook.Sheets(1)
wb.Close False
End Sub

I now want a seperate macro that can delete all of these new sheets.

Somebody has told me to use:-
Application.DisplayAlerts=False
worksheets(n).Delete
Application.DisplayAlerts=True

but I can't seem to just paste this into a new macro.

Any help would be greatly appreciated!


---
Message posted from http://www.ExcelForum.com/