• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Crashes after running code.

JD Hernandez

New Member
Hey everyone.
So, I have this bit of code that I put together and it was working fine up to this point. It crashes my excel and gives me an error about the object invoked disconnected from client. The basic of what I'm trying to do is that I have one excel sheet that Input all my data for a particular property and then I save that sheet into another workbook with a new name that is designated in cell B2. And then, another workbook opens and puts in a table that summarizes all the tabs in the previous workbook. I'm not all to worried about the code that makes the summary cause Excel crashes in this first part of the code where it says 'Copy and Rename General Info Tab to General Info Template. Can someone take a look at my code and see if I'm doing anything wrong. I'm still pretty new at VBA, so it is likely I didn't copy a code right. I'll keep searching for an answer through the web as well.

Code:
Sub New_Button()

Application.ScreenUpdating = False


Dim x As Workbook, y As Workbook, ws As Worksheet

Dim z As Workbook


Set x = Workbooks.Open("\\DCSAHT01\Company\SAHT PFC-FC PROJECTS\Project General Info Template.xlsm")

Set y = Workbooks.Open("\\DCSAHT01\Company\SAHT PFC-FC PROJECTS\Portfolio Tracking.xlsm")

Set z = Workbooks.Open("\\DCSAHT01\Company\SAHT PFC-FC PROJECTS\Financial Dashboard.xlsm")


'Copy and rename General Info Tab to General Info Template.

Sheets("General Info").Copy After:=Workbooks("Project General Info Template.xlsm").Sheets("Summary")

For Each ws In Worksheets

  With ws

  If .Range("B2").Value <> "" Then .Name = .Range("B2").Value

  End With

Next ws


'Delete Command Button from copied sheet

On Error Resume Next

  For i = 3 To 10000

  ActiveWorkbook.Sheets(i).Buttons.Delete

  Next i


End Sub
 
In which workbook Sheets("General Info") exits?

Change it & check
Len(.Range("B2").Value)>0

In which workbook renaming being happen?

Loop also not required in fast galnce.
 
In which workbook Sheets("General Info") exits?

Change it & check
Len(.Range("B2").Value)>0

In which workbook renaming being happen?

Loop also not required in fast galnce.
The general info sheet is in the Financial Dashboard workbook and the renaming takes place in the Project General Info Template workbook. What loop are you referring to? I'll try the change and see what it does. Also, I tried redoing the financial dashboard in another exccel workbook, and this seems to be working fine for the time being. I'm sure something will go wrong again. But, for now it is working.
 
Last edited:
Back
Top