Excel greyed after vba has run,

Saab95

New Member
Joined
Mar 26, 2021
Messages
40
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

Attachments

  • Screenshot 2021-04-15 165851.jpg
    Screenshot 2021-04-15 165851.jpg
    62.7 KB · Views: 13
  • Screenshot 2021-04-15 170354.jpg
    Screenshot 2021-04-15 170354.jpg
    80.3 KB · Views: 14

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Are your sheets hidden?

Also, what is the purpose of this line?
VBA Code:
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.
 
Upvote 0
Are your sheets hidden?

Also, what is the purpose of this line?
VBA Code:
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.
 
Upvote 0
You did not answer my first question.
Are your other sheets hidden?

I also notice another issue. These lines you have at the bottom:
Rich (BB code):
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.
 
Upvote 0
You did not answer my first question.
Are your other sheets hidden?

I also notice another issue. These lines you have at the bottom:
Rich (BB code):
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
 
Upvote 0
What is the name of the module this VBA code is located in?

Try this and see if it makes any difference:
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top