This is strange and may be helpful for others.
1. The "conditional Formatting" was not causing the problem, it only
apeared that way.
2. This is a simple matter of worksheet naming. In Excel 2007 it appears
that worksheet names can not conflict with the manner that Excel renames when
making coppies. Example: If I name a worksheet "OP 10 (1)" this is a direct
conflict with Excel naming.
If the worksheet is named "OP 10-1" when you make a copy Excel renames to
"OP 10-1 (1)". Hence Excel 2007 reads the first worksheet I named "OP 10
(1)" as a iteration(?) and begins the copy name at "OP 10 (2)" this
apparently causes a counting conflict which is the problem that occurred with
my macro.
HTH others.
--
Regards
VBA.Newb.Confused
XP Pro
Office 2007
"Rick S." wrote:
> How can conditional formatting crash this macro?
> It will fail at "Calculate" when some conditional formatting is used,
> unfortunately I have not found the exact formatting that causes the crash. I
> can circumvent the crash if I clear all conditionals prior to running the
> macro. However it has adverse affects as well, like failing to copy
> worksheets to the proper location of each other.
>
> I can supply a sample workbook with all conditional formatting and releavant
> macros to any who wish to help. The macro is a printing function.
> IPI = In-Process Inspection (report).
>
> '======
> Sub PrintIPI()
> '
> ' PrintIPI Macro
> ' Macro date 03/08/07 by L Roach
> 'This macro prints and sorts the correct number of copies of the IPI based on
> 'the number of parts on the job.
>
> Dim NumCopies As Long, WhereToPaste As Long, PasteCopy As Long, WhatOp As Long
> Dim GetName() As String, LastPage As String, OpName As String
> Dim objSht As Worksheet
> On Error GoTo SORRY
> Calculate
> 'If there are more than 50 parts add the required sheets
> If Sheets("Master Sheet").Range("B21").Value > 50 Then
> NumCopies = Round((Sheets("Master Sheet").Range("B21").Value / 50) +
> 0.5) - 1
> For Each objSht In ActiveWorkbook.Worksheets
> For PasteCopy = 1 To NumCopies
> 'Name the pages that have been added for extra parts
> If InStr(UCase(objSht.Name), "OP") <> 0 Then
> GetName = Split(Trim(Replace((Replace(UCase(objSht.Name),
> "OP", "")), "-", " ")), " ")
> OpName = GetName(0)
> For WhatOp = objSht.Index To Sheets.Count
> If InStr(Sheets(WhatOp).Name, OpName) < 1 Then Exit For
> If InStr(Sheets(WhatOp).Name, LastPage & " (" &
> PasteCopy + 1 & ")") > 0 Then
> WhatOp = WhatOp + 1
> Exit For
> End If
> Next WhatOp
> 'Fix the wording on the in process sheets
> Sheets(objSht.Name).Copy after:=Sheets(WhatOp - 1)
> Cells.Replace What:="Oper F.A.", Replacement:="I.P.",
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False,
> SearchFormat:=False, _
> ReplaceFormat:=False
> Cells.Replace What:="Insp F.A.", Replacement:="I.P.",
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False,
> SearchFormat:=False, _
> ReplaceFormat:=False
> Cells.Replace What:="Co Op F.A.", Replacement:="I.P.",
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False,
> SearchFormat:=False, _
> ReplaceFormat:=False
> End If
> Next PasteCopy
> LastPage = objSht.Name
> Next objSht
> End If
> Application.CutCopyMode = False
> ActiveWorkbook.PrintOut
> ActiveWindow.Close SaveChanges:=False
> Exit Sub
> SORRY:
> MsgBox "Failed to print IPI", , "Sorry... Had an error."
> End Sub
> '======
> --
> Regards
>
> VBA.Newb.Confused
> XP Pro
> Office 2007
>
|
Similar Threads
