Question:

Hi, I am working on an Excel file to submit worksheets to another file (Mastertable.xlsx) and delete sheets with same name in destination file if it is already exist.  I have 3 sets of the following codes for 3 different sheets.  I used it with other routines and they are working fine.  The problem is when I tried to run them together back to back the system is not responding.  Can you tell what went wrong?  Thanks.

Sub Submit_1()

Application.DisplayAlerts = False

Dim filename As String, x As String
Dim ws As Worksheet, i As String

filename = ActiveWorkbook.Name

Workbooks.Open “C:\My Documents\MasterTables.xlsx”, UpdateLinks:=0

x = ActiveWorkbook.Name
For Each ws In ActiveWorkbook.Worksheets
If i = “” Then
i = ws.Name
Else
i = i & “,” & ws.Name
End If
Next ws

Workbooks(filename).Activate

SheetCounter = 0

For Each ws In ActiveWorkbook.Worksheets

If InStr(ws.Name, “Sheet1″) > 0 Then
If InStr(i, ws.Name) > 0 Then

UnprotectSheet

Workbooks(x).Activate
Sheets(ws.Name).Delete
Workbooks(filename).Activate
ws.Copy before:=Workbooks(x).Sheets(“E”)

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Else
ws.Copy before:=Workbooks(x).Sheets(“E”)

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If
Workbooks(filename).Activate

SheetCounter = SheetCounter + 1
End If
ProtectSheet
Next ws

Workbooks(“MasterTables.xlsx”).Save
Workbooks(“MasterTables.xlsx”).Close
Application.DisplayAlerts = True

End Sub

Solution:

The use of the same that its opening your file and in case if its read only it will take care of that message as well and no you need the same there as you need to open that file to do operations over it.

Saurabh…