Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSCR Error
We have an error that is suddenly coming up in our application.
After we Run ALL Macros, we receive two errors: First there is a runtime error that states: Appilication Error The message filter indicated that the application is busy. The second message state: File in Use MACROS.XLS is locked for editing by 'DSCR' Open 'Read Only' or Click 'Notify; to open read-only and receive notification whne the document is no longer in use. This is a new error for the database users. We're not sure what it stems from. Any advice would be greatly appreciated. Thanks in advance! ~Elena |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSCR Error
-1- Reboot the machine, see if it helps.
-2- If not, post your "All Macros" here; someone might be able to help. Make sure that the code doesn't contain any sensitive infomation like user name + password for your db. arunkhemlai "Elena" wrote: We have an error that is suddenly coming up in our application. After we Run ALL Macros, we receive two errors: First there is a runtime error that states: Appilication Error The message filter indicated that the application is busy. The second message state: File in Use MACROS.XLS is locked for editing by 'DSCR' Open 'Read Only' or Click 'Notify; to open read-only and receive notification whne the document is no longer in use. This is a new error for the database users. We're not sure what it stems from. Any advice would be greatly appreciated. Thanks in advance! ~Elena |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSCR Error
Here's the Code. It's lengthy. Thanks :)
Sub OPEN_PR_REPORT_FORMATTING_TOOL() ' ' OpenPr Macro ' Macro recorded 9/28/2004 by Evansdc Dim MYSHEET As Worksheet For Each MYSHEET In Worksheets If Left(MYSHEET.Name, Len("OPEN_PRS")) = "OPEN_PRS" Then MYSHEET.Name = "OPEN_PRS" End If If Left(MYSHEET.Name, Len("REPLEN_PRS_ALT")) = "REPLEN_PRS_ALT" Then MYSHEET.Name = "REPLEN_PRS_ALT" End If If Left(MYSHEET.Name, Len("PRECONTDEL")) = "PRECONTDEL" Then MYSHEET.Name = "PRECONTDEL" End If If Left(MYSHEET.Name, Len("CONTPLT")) = "CONTPLT" Then MYSHEET.Name = "CONTPLT" End If MYSHEET.Activate Rows("2:2").Select ActiveWindow.FreezePanes = True Range("A1").Select Next Set MYSHEET = Nothing Sheets("OPEN_PRS").Select Range("A1:AA1").Select Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) ' .LineStyle = xlContinuous ' .Weight = xlThin ' .ColorIndex = xlAutomatic End With With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Select With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Cells.EntireColumn.AutoFit Range("A1:AA1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Columns("A:A").ColumnWidth = 5.14 Rows("1:1").EntireRow.AutoFit Columns("A:A").ColumnWidth = 4.57 Columns("A:A").EntireColumn.AutoFit Columns("B:B").ColumnWidth = 6.86 Columns("B:B").EntireColumn.AutoFit Columns("E:E").ColumnWidth = 3.86 Rows("1:1").EntireRow.AutoFit Columns("F:F").ColumnWidth = 9 Columns("F:F").Select Selection.ColumnWidth = 6.43 Columns("F:F").EntireColumn.AutoFit Columns("G:G").ColumnWidth = 6.43 Columns("G:G").EntireColumn.AutoFit Columns("H:H").ColumnWidth = 6.14 Columns("H:H").EntireColumn.AutoFit Columns("I:I").ColumnWidth = 4.14 Columns("I:I").EntireColumn.AutoFit Columns("J:J").ColumnWidth = 7 Columns("J:J").EntireColumn.AutoFit Columns("K:K").ColumnWidth = 6.14 Columns("K:K").EntireColumn.AutoFit Columns("K:K").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Columns("M:M").ColumnWidth = 6 Columns("N:N").ColumnWidth = 5.71 Columns("O:O").ColumnWidth = 10.29 Columns("P:P").ColumnWidth = 6.57 Rows("1:1").EntireRow.AutoFit Columns("P:P").EntireColumn.AutoFit Columns("O:O").EntireColumn.AutoFit Columns("Q:Q").ColumnWidth = 6.71 Columns("Q:Q").EntireColumn.AutoFit Columns("R:R").ColumnWidth = 6 Columns("R:R").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=8 Columns("T:T").ColumnWidth = 4.86 Columns("T:T").EntireColumn.AutoFit Columns("U:U").ColumnWidth = 6.86 Columns("U:V").Select Selection.NumberFormat = "$#,##0.00" Columns("U:V").EntireColumn.AutoFit Columns("W:W").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With ActiveWindow.SmallScroll ToRight:=4 ActiveWindow.SmallScroll Down:=-9 Columns("AA:AA").ColumnWidth = 7.57 Columns("AA:AA").EntireColumn.AutoFit Range("A2").Select 'Sheets("OPEN_PRS").Select Sheets("OPEN_PRS").Name = "OPEN PRS" Sheets("OPEN PRS").Select Range("A2").Select With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "&A" .RightHeader = "" .LeftFooter = "" .CenterFooter = "Page &P of &N" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 90 End With With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "&A" .RightHeader = "" .LeftFooter = "" .CenterFooter = "Page &P of &N" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 85 End With Range("A1").Select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxx Sheets("REPLEN_PRS_ALT").Name = "REPLEN PRSALT" Sheets("REPLEN PRSALT").Select Range("A1:T1").Select Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) ' .LineStyle = xlContinuous ' .Weight = xlThin ' .ColorIndex = xlAutomatic End With With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Select With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Range("A1").Select Columns("A:A").ColumnWidth = 5.71 Columns("A:A").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Cells.Select Selection.ColumnWidth = 4.29 Range("A2").Select Columns("E:E").EntireColumn.AutoFit Columns("F:F").ColumnWidth = 4.57 Columns("G:G").EntireColumn.AutoFit Columns("H:H").EntireColumn.AutoFit Columns("I:I").EntireColumn.AutoFit Columns("J:J").EntireColumn.AutoFit Columns("K:K").EntireColumn.AutoFit Columns("L:L").EntireColumn.AutoFit Columns("M:M").EntireColumn.AutoFit Columns("M:M").ColumnWidth = 8 Columns("N:N").ColumnWidth = 6.14 Columns("N:N").ColumnWidth = 5.57 Columns("N:N").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Columns("O:O").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Columns("P:P").ColumnWidth = 6.43 Columns("Q:Q").ColumnWidth = 6.57 Columns("Q:Q").ColumnWidth = 5.43 Columns("R:R").ColumnWidth = 11.29 Rows("1:1").EntireRow.AutoFit ActiveWindow.SmallScroll ToRight:=3 Columns("R:R").ColumnWidth = 10.57 Columns("R:R").EntireColumn.AutoFit Columns("S:S").ColumnWidth = 7.43 Columns("S:S").EntireColumn.AutoFit Columns("T:T").ColumnWidth = 13.29 Columns("T:T").ColumnWidth = 8.29 Columns("T:T").EntireColumn.AutoFit Rows("1:1").EntireRow.AutoFit 'Sheets("REPLEN_PRS_ALT").Select ActiveWindow.ScrollColumn = 1 'Sheets("REPLEN_PRS_ALT").Name = "REPLEN PRSALT" 'Sheets("REPLEN PRSALT").Select Range("A2").Select With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "&A" .RightHeader = "" .LeftFooter = "" .CenterFooter = "Page &P of &N" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 90 End With Columns("K:K").ColumnWidth = 4.29 Columns("B:B").ColumnWidth = 8.71 Columns("B:B").EntireColumn.AutoFit ActiveWindow.SmallScroll Down:=-15 Sheets("PRECONTDEL").Select Range(Selection, Selection.End(xlToRight)).Select Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) ' .LineStyle = xlContinuous ' .Weight = xlThin ' .ColorIndex = xlAutomatic End With With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Select With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Cells.EntireColumn.AutoFit Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Range("A2").Select Columns("D:D").ColumnWidth = 5.29 Rows("1:1").EntireRow.AutoFit Columns("D:D").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=3 Columns("G:G").ColumnWidth = 10.43 Columns("G:G").ColumnWidth = 9 Columns("G:G").EntireColumn.AutoFit Columns("G:G").ColumnWidth = 8.57 Rows("1:1").EntireRow.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("J:J").ColumnWidth = 5.71 Columns("J:J").EntireColumn.AutoFit Columns("M:M").ColumnWidth = 6.29 Columns("M:M").EntireColumn.AutoFit Columns("O:O").ColumnWidth = 6.14 Columns("O:O").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=7 Columns("P:P").ColumnWidth = 14.57 Columns("P:P").ColumnWidth = 9.43 Rows("1:1").EntireRow.AutoFit Columns("Q:Q").ColumnWidth = 5.57 Columns("Q:Q").EntireColumn.AutoFit Columns("R:R").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Columns("S:S").ColumnWidth = 6 Columns("S:S").EntireColumn.AutoFit Columns("T:T").ColumnWidth = 4.86 Columns("T:T").EntireColumn.AutoFit ActiveWindow.ScrollColumn = 1 Range("A1").Select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxx 'Sheets("PRECONTDEL").Select 'Sheets("PRECONTDEL").Name = "PRECONTDEL" Sheets("PRECONTDEL").Select Range("A2").Select With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "&A" .RightHeader = "" .LeftFooter = "" .CenterFooter = "Page &P of &N" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 90 End With Sheets("CONTPLT").Select Range(Selection, Selection.End(xlToRight)).Select Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) ' .LineStyle = xlContinuous ' .Weight = xlThin ' .ColorIndex = xlAutomatic End With With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Cells.Select With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Range("A2").Select Columns("D:D").ColumnWidth = 6.43 Rows("1:1").EntireRow.AutoFit Columns("D:D").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=3 Columns("F:F").ColumnWidth = 8 Rows("1:1").RowHeight = 23.25 Rows("1:1").EntireRow.AutoFit Columns("J:J").ColumnWidth = 4.86 Columns("J:J").EntireColumn.AutoFit Columns("K:K").EntireColumn.AutoFit ActiveWindow.SmallScroll ToRight:=3 Columns("O:O").ColumnWidth = 6.14 Columns("O:O").EntireColumn.AutoFit Columns("Q:Q").ColumnWidth = 5.43 Columns("Q:Q").EntireColumn.AutoFit Columns("R:R").ColumnWidth = 12.14 Rows("1:1").EntireRow.AutoFit Columns("S:S").ColumnWidth = 5.86 Columns("S:S").EntireColumn.AutoFit Columns("T:T").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Columns("U:U").ColumnWidth = 5.14 Columns("U:U").ColumnWidth = 5 Columns("U:U").EntireColumn.AutoFit Columns("V:V").ColumnWidth = 4.43 Columns("V:V").EntireColumn.AutoFit Range("N1").Select ' Range("N1").AddComment ' Range("N1").Comment.Visible = False ' Range("N1").Comment.Text Text:="PLT Late= (Current Date - Award Date) - PLT" & Chr(10) & "" ' Selection.ShapeRange.ScaleWidth 2.15, msoFalse, msoScaleFromTopLeft ' Selection.ShapeRange.ScaleHeight 0.46, msoFalse, msoScaleFromTopLeft Range("A2").Select With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "&A" .RightHeader = "" .LeftFooter = "" .CenterFooter = "Page &P of &N" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 90 End With Range("A1").Select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxx Sheets("CONTPLT").Select 'Sheets("CONTPLT__09_28_2004_").Name = "CONTPLT" Range("A2").Select ActiveWindow.SmallScroll ToRight:=5 Columns("N:N").ColumnWidth = 7.14 Columns("N:N").EntireColumn.AutoFit ActiveWindow.ScrollColumn = 1 With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "&A" .RightHeader = "" .LeftFooter = "" .CenterFooter = "Page &P of &N" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 85 End With ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 1 With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "&A" .RightHeader = "" .LeftFooter = "" .CenterFooter = "Page &P of &N" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 80 End With 'the code below adds the comment 'Sheets("CONTPLT").Select Range("N1").Select Range("N1").AddComment Range("N1").Comment.Visible = False Range("N1").Comment.Text Text:= _ "PLT LATE = (CURRENT DATE - AWARD DATE) - (PLT)" ' Selection.ShapeRange.ScaleWidth 3.27, msoFalse, msoScaleFromTopLeft ' Selection.ShapeRange.ScaleHeight 0.26, msoFalse, msoScaleFromTopLeft ' Selection.ShapeRange.ScaleWidth 0.71, msoFalse, msoScaleFromTopLeft ' Selection.ShapeRange.ScaleHeight 0.84, msoFalse, msoScaleFromTopLeft Range("A1").Select Sheets("OPEN PRS").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSCR Error
Elena,
Do you think you step through the code and give a hint where the error occurs ? Also, it would greatly shorten your code if you deleted all the irrelevant code that the macro recorder generates: e.g. Select, properties that remain at the default values, tidy up the Withs, etc Also you have many occurences of: Columns("A:A").ColumnWidth = 5.71 Columns("A:A").EntireColumn.AutoFit Presumably you want the AutoFit not 5.71. "DSCR" appears to be something connected with financial services. NickHK "Elena" wrote in message ... Here's the Code. It's lengthy. Thanks :) Sub OPEN_PR_REPORT_FORMATTING_TOOL() ' ' OpenPr Macro ' Macro recorded 9/28/2004 by Evansdc Dim MYSHEET As Worksheet For Each MYSHEET In Worksheets If Left(MYSHEET.Name, Len("OPEN_PRS")) = "OPEN_PRS" Then MYSHEET.Name = "OPEN_PRS" End If If Left(MYSHEET.Name, Len("REPLEN_PRS_ALT")) = "REPLEN_PRS_ALT" Then MYSHEET.Name = "REPLEN_PRS_ALT" End If If Left(MYSHEET.Name, Len("PRECONTDEL")) = "PRECONTDEL" Then MYSHEET.Name = "PRECONTDEL" -------------- CODE SNIPPED -------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error: "Excel encountered an error and had to remove some formatti | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming |