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.
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