Excel greyed after vba has run,

Saab95

New Member
Joined
Mar 26, 2021
Messages
24
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: 8
  • Screenshot 2021-04-15 170354.jpg
    Screenshot 2021-04-15 170354.jpg
    80.3 KB · Views: 8

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,508
Office Version
  1. 365
Platform
  1. Windows
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.
 

Saab95

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,508
Office Version
  1. 365
Platform
  1. Windows
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.
 

Saab95

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,508
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,644
Messages
5,654,552
Members
418,140
Latest member
ahepple86

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
Top