Unable to save xlsm - errors detected while saving

[srdavisgb]

I have created a macro that creates worksheets from a template and moves them to a workbook. The program runs until it encounters a condition where only one worksheet is being saved to the workbook. When I tried to save the workbook that the program stopped on, I got an "Errors detected while saving..." error message. The program successfully built and saved over 15 workbooks so I don't think it's the template. I'm not sure about the VBA. In any event, how do you find the errors in the workbook? The error message says Excel may be able to save the file by removing or repairing some features - pretty vague.

Thanks for your help in advance.

 

[patel45]

why not show us your code ?

 

[srdavisgb]

Sub create_CS_Doc()

Dim TotRows As Integer
…….

Workbooks("Create Statements.xlsm").Activate
TotRows = Sheets("Merge Wkshts").Cells(1, 1)
CurrRow = 2

Workbooks("Create Statements.xlsm").Activate
TotRows2 = Sheets("Pivot Table").Cells(1, 1)
CurrRow2 = 2

For I = 1 To TotRows

StartLine = Sheets("Pivot Table").Cells(CurrRow2, 5)
EndLine = Sheets("Pivot Table").Cells(CurrRow2, 6)
EECount = Sheets("Pivot Table").Cells(CurrRow2, 4)
RowNo = Sheets("Merge Wkshts").Cells(CurrRow, 90)
Filename = Sheets("Merge Wkshts").Cells(CurrRow, 91)
GrpCtr = Sheets("Merge Wkshts").Cells(CurrRow, 92)
GrpCounterNext = Sheets("Merge Wkshts").Cells(CurrRow + 1, 92)
CS_Type = Sheets("Merge Wkshts").Cells(CurrRow, 98)
CreateCS = Sheets("Merge Wkshts").Cells(CurrRow, 99)
ICSFilename = Sheets("Merge Wkshts").Cells(CurrRow, 100)


If GrpCtr = 1 Then
Workbooks.Open Filename:=Grp_ECS_Path & "\" & " Group Statement.xlsm"
………

'Copy Name into Worksheet
Windows("Create Statements.xlsm").Activate
Sheets("Merge Wkshts").Select
RangeArea = "B" & StartLine & ":B" & EndLine
Range(RangeArea).Select
Selection.Copy
Windows("Group Statement.xlsm").Activate
Sheets("Data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
……………

Range("A1").Select
CurrRow2 = CurrRow
End If

If CS_Type = 1 Then
Workbooks.Open Filename:=ECS_Template_Path & "\ Individual CS.xlsx"

'Enter Demographic information
Range("C7").Select
ActiveCell.FormulaR1C1 = Name
………….
Workbooks("Individual CS.xlsx").Activate
Sheets("Name").Select
Sheets("Name").Name = Name
ActiveSheet.Protect Password:="KSS2013", DrawingObjects:=True, Contents:=True, Scenarios:=True,
AllowFormattingRows:=False, AllowSorting:=False, AllowFiltering:=False
Sheets(Name).Copy After:=Workbooks("Group Statement.xlsm").Sheets(4)
…….
Windows("Individual CS.xlsx").Activate
ActiveWorkbook.Close SaveChanges:=False
End If

If CS_Type = 2 Then
Workbooks.Open Filename:=ECS_Template_Path & "\Second Individual CS.xlsx"

'Enter Demographic information
Range("C7").Select
……….
End If

'Save Grp Workbook
If GrpCounterNext = 1 Or GrpCounterNext = "" Then

Windows("Group Statement.xlsm").Activate
ActiveWorkbook.SaveAs Filename:=Grp_ECS_Save_Dir & "\" & Filename _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End If

CurrRow = CurrRow + 1
Workbooks("Create Statements.xlsm").Activate
Range("A1").Select

Next I

End Sub

I think the issue is with the GrpCounterNext code:

If GrpCounterNext = 1 Or GrpCounterNext = "" Then

Windows("Group Statement.xlsm").Activate
ActiveWorkbook.SaveAs Filename:=Grp_ECS_Save_Dir & "\" & Filename _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End If

Below is what the data looks like in the "Merge Wkshts" worksheet:
Filename GrpCtr
Group_1.xlsx 1
Group_2.xlsx 1
Group_2.xlsx 2
Group_3.xlsx 1
Group_3.xlsx 2
Group_3.xlsx 3

Each row represents a worksheet that is being built into the workbook. Group_1 only has one worksheet. When GrpCounterNext = 1, then the program saves the file and the program should start another workbook (e.g., Group_2). It works for all other rows except when there are two consecutive rows where the GrpCtr =1.

Any ideas? Again, thanks for your help.

 

[patel45]

please upload a sample file on a share site then paste the link here

 

[srdavisgb]

please upload a sample file on a share site then paste the link here

http://wikisend.com/download/352888/Hamilton Individual CS_test.xlsx
Wikisend: free file sharing service
Wikisend: free file sharing service
Wikisend: free file sharing service

The Create SSS Comp Statements_test file (the last Wikisend link) has the macro that creates the workbooks. The other three files are templates that are used by the macro. In the Comp Statement Requirements worksheet, identify where you have saved the files and click on the button in Step 3 to launch the macro. When you open this file, don't update the links. I haven't figure-out how to remove them, yet.

Good Luck.

 

[patel45]

the problem is here

ActiveWorkbook.SaveAs Filename:=Grp_ECS_Save_Dir & "\" & Filename _
           , FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
           ReadOnlyRecommended:=False, CreateBackup:=False

I think you forgot to unprotect the document before saving

 

[srdavisgb]

the problem is here

ActiveWorkbook.SaveAs Filename:=Grp_ECS_Save_Dir & "\" & Filename _
           , FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
           ReadOnlyRecommended:=False, CreateBackup:=False

I think you forgot to unprotect the document before saving

Thanks for your reply. You are correct, this is where the code stops. However, no where in the code did I protect the document (e.g., workbook). I did protect the worksheets, which I need to keep protected. What's interesting is the code worked on all the other documents (that have been protected) leading up to one workbook with only one worksheet in it...

The error message says the workbook contains errors. One thing I did discover is if you remove the logo on the worksheet in the workbook that does not save, you can save it. Again, I don't know why this would stop the program because it's the same logo used in all the other workbooks.

Any ideas why?

 

[patel45]

The error message does not say the workbook contains errors, but excel can not save document