Excel greyed after vba has run,

[Saab95]

Hi,

I have this vba to combine sheets. When I run it I have no errors.

Sub CombineWorkbooks()

Dim Path As String
Path = "N:\Bridge_delivery_file\"

Dim FileName As String
FileName = Dir(Path & "*.xl??")

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While FileName <> ""
Workbooks.Open Path & FileName
For Each ws In ActiveWorkbook.Sheets
ws.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Next ws
Workbooks(FileName).Close
FileName = Dir()
Loop

Worksheets(1).Delete

Application.ScreenUpdating = False
Application.DisplayAlerts = False

End Sub

But when I have finished to run it, looks like my excel crashed or something. It looks like that: all grey

But in the developper, I see some sheets being added (not all and some are duplicates). But I cannot see them inthe excel.

Strange no ?

 

[Joe4]

Are your sheets hidden?

Also, what is the purpose of this line?

Worksheets(1).Delete

It seems to be running AFTER you have closed the workbook you opened to work on, meaning that it will delete the first sheet the workbook the VBA code is located in.

 

[Saab95]

Are your sheets hidden?

Also, what is the purpose of this line?

Worksheets(1).Delete

It seems to be running AFTER you have closed the workbook you opened to work on, meaning that it will delete the first sheet the workbook the VBA code is located in.

Hi,

Thanks for your reply.

I have deleted
Worksheets(1).Delete
and same results.

Excel is just like in close status.

 

[Joe4]

You did not answer my first question.
Are your other sheets hidden?

I also notice another issue. These lines you have at the bottom:

Application.ScreenUpdating = False
Application.DisplayAlerts = False

End Sub

Those two values need to be set to "True" at the end of your code.
You typically only shut them off while the code is running, but will want to turn them back on at the end.

 

[Saab95]

You did not answer my first question.
Are your other sheets hidden?

I also notice another issue. These lines you have at the bottom:

Application.ScreenUpdating = False
Application.DisplayAlerts = False

End Sub

Those two values need to be set to "True" at the end of your code.
You typically only shut them off while the code is running, but will want to turn them back on at the end.

No nothing is hidden, and replacing
Application.ScreenUpdating = False True
Application.DisplayAlerts = False True

makes same result

 

[Joe4]

What is the name of the module this VBA code is located in?

Try this and see if it makes any difference:

Sub CombineWorkbooks()

Dim wb as Workbook
Set wb = ActiveWorkbook

Dim Path As String
Path = "N:\Bridge_delivery_file\"

Dim FileName As String
FileName = Dir(Path & "*.xl??")

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While FileName <> ""
Workbooks.Open Path & FileName
For Each ws In ActiveWorkbook.Sheets
ws.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Next ws
Workbooks(FileName).Close
FileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

wb.Activate

End Sub