B
Bud
Hello
Question is how can i get to where the workbook will open in less than a
second again? I build 11 out of 133 worksheets through 4 macros.
I have 4 macros in a workbook. None of them are very long but there are 13
worksheets. None of those really goes beyond 5,000 lines.
When I was working with this it did run up to 30 seconds to open this but I
went ahead and copied it over to an HTML file and recopied it back and than
it reduced the size of the workbook and it than opened right away.
I tried that again but it didn't work this time.
If I hit crtl + shift + the down arrow it runs down to 65,+ lines on every
worksheet. I don't think that is the problem because I can open ANY workbook
and do this and it does the same.
I copy in 2 worksheets and build all the rest through the macros
automatically. So....11 worksheets are being updated.
In some cases I completely delete the worksheet re-add it and than completly
copy the worksheet.
In other cases I Clearcontents of the entire worksheet and than copy over
another worksheet.
Here is the code from the largest of the 4 macros
Sub SueHMacro()
'
' SueHMacro Macro
' Macro recorded 9/6/2008 by czj63c
'
' Keyboard Shortcut: Ctrl+Shift+R
'
'This next statement turns off the screen updating while the macro is
running
Application.ScreenUpdating = False
'Start of selecting Save-All deleting and than re-creating
Sheets("Save-All").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Save-All"
'End of selecting Save-All deleting and than re-creating
'Copies ALL the SAP transactions with Clarity nbr(Fill-Down)to a save file
' Pastes the values and numbers themselves NOT the formulas
Sheets("Fill-Down").Select
Cells.Select
Selection.Copy
Sheets("Save-All").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns("A:A").ColumnWidth = 35
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 10
Columns("D").ColumnWidth = 48
Columns("E:E").ColumnWidth = 14
Columns("F:F").ColumnWidth = 5
'Start of selecting Save-Enh deleting and than re-creating
Sheets("Save-Enh").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Save-Enh"
'End of selecting Save-Enh deleting and than re-creating
Sheets("Save-All").Select
Cells.Select
Selection.Copy
Sheets("Save-Enh").Select
Cells.Select
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 35
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 10
Columns("D").ColumnWidth = 48
Columns("E:E").ColumnWidth = 14
Columns("F:F").ColumnWidth = 5
' Filter Save-Enh for EN
Sheets("Save-Enh").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="EN"
'Selection.AutoFilter Field:=6, Criteria1:="EN", Operator:=xlOr, _
' Criteria2:="=HD"
'Selection.Copy '????????????????????????????????????????????????
'End of filtering EN for Save-Enh
'Start of selecting ClientReport deleting and than re-creating
Sheets("ClientReport").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "ClientReport"
'End of selecting ClientReport deleting and than re-creating
'Takes the saved Enhancements and copies to the ClientReport
Sheets("Save-Enh").Select
Cells.Select
Selection.Copy
Sheets("ClientReport").Select
Cells.Select
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 35
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 10
Columns("D").ColumnWidth = 48
Columns("E:E").ColumnWidth = 14
Columns("F:F").ColumnWidth = 5
'End of paste filter Save_Enh into ClientReport
'Start of selecting Enh-Total deleting and than re-creating
Sheets("Enh-Total").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Enh-Total"
'End of selecting Enh-Total deleting and than re-creating
'
'Start of Sub total on Enhancements
Sheets("Save-Enh").Select
Selection.Copy
Sheets("Enh-Total").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 25
Columns("D").ColumnWidth = 67
Columns("E:E").ColumnWidth = 15
Sheets("Enh-Total").Select
Cells.Select
Range("A1").Select
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
'Start of selecting All_Total deleting and than re-creating
Sheets("All_Total").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "All_Total"
'End of selecting All_Total deleting and than re-creating
'Start of sub total for all records
Sheets("Save-All").Select
Selection.Copy
Sheets("All_Total").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 25
Columns("D").ColumnWidth = 67
Columns("E:E").ColumnWidth = 15
Sheets("All_Total").Select
Cells.Select
Range("A1").Select
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
'Replaces #Ref with "All_Total for the formula to work. It gets messed
up in a file rebuild
' This automatically posts the SAP totals to a worksheet
Sheets("Actuals-PIV").Select
Columns("A:A").Select
Selection.Replace What:="#REF", Replacement:="All_Total", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'Clear all contents of worksheet except for the names of the column
fields and the formulas in row 2
Sheets("Fill-Down").Select
Range("A3:F3000").Select
Selection.Clear
'Sheets("Fill-Down").Select
'Cells.Select
'Selection.Copy
'Sheets("FillSave").Select
'Range("A1").Select
'ActiveSheet.Paste
'Columns("A:A").ColumnWidth = 35
'Columns("B:B").ColumnWidth = 11
'Columns("C:C").ColumnWidth = 10
'Columns("D").ColumnWidth = 48
'Columns("E:E").ColumnWidth = 14
'Columns("F:F").ColumnWidth = 5
'Sheets("Fill-Down").Select
'Range("A3:F3000").Select
'Selection.Clear
End Sub
Question is how can i get to where the workbook will open in less than a
second again? I build 11 out of 133 worksheets through 4 macros.
I have 4 macros in a workbook. None of them are very long but there are 13
worksheets. None of those really goes beyond 5,000 lines.
When I was working with this it did run up to 30 seconds to open this but I
went ahead and copied it over to an HTML file and recopied it back and than
it reduced the size of the workbook and it than opened right away.
I tried that again but it didn't work this time.
If I hit crtl + shift + the down arrow it runs down to 65,+ lines on every
worksheet. I don't think that is the problem because I can open ANY workbook
and do this and it does the same.
I copy in 2 worksheets and build all the rest through the macros
automatically. So....11 worksheets are being updated.
In some cases I completely delete the worksheet re-add it and than completly
copy the worksheet.
In other cases I Clearcontents of the entire worksheet and than copy over
another worksheet.
Here is the code from the largest of the 4 macros
Sub SueHMacro()
'
' SueHMacro Macro
' Macro recorded 9/6/2008 by czj63c
'
' Keyboard Shortcut: Ctrl+Shift+R
'
'This next statement turns off the screen updating while the macro is
running
Application.ScreenUpdating = False
'Start of selecting Save-All deleting and than re-creating
Sheets("Save-All").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Save-All"
'End of selecting Save-All deleting and than re-creating
'Copies ALL the SAP transactions with Clarity nbr(Fill-Down)to a save file
' Pastes the values and numbers themselves NOT the formulas
Sheets("Fill-Down").Select
Cells.Select
Selection.Copy
Sheets("Save-All").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns("A:A").ColumnWidth = 35
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 10
Columns("D").ColumnWidth = 48
Columns("E:E").ColumnWidth = 14
Columns("F:F").ColumnWidth = 5
'Start of selecting Save-Enh deleting and than re-creating
Sheets("Save-Enh").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Save-Enh"
'End of selecting Save-Enh deleting and than re-creating
Sheets("Save-All").Select
Cells.Select
Selection.Copy
Sheets("Save-Enh").Select
Cells.Select
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 35
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 10
Columns("D").ColumnWidth = 48
Columns("E:E").ColumnWidth = 14
Columns("F:F").ColumnWidth = 5
' Filter Save-Enh for EN
Sheets("Save-Enh").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="EN"
'Selection.AutoFilter Field:=6, Criteria1:="EN", Operator:=xlOr, _
' Criteria2:="=HD"
'Selection.Copy '????????????????????????????????????????????????
'End of filtering EN for Save-Enh
'Start of selecting ClientReport deleting and than re-creating
Sheets("ClientReport").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "ClientReport"
'End of selecting ClientReport deleting and than re-creating
'Takes the saved Enhancements and copies to the ClientReport
Sheets("Save-Enh").Select
Cells.Select
Selection.Copy
Sheets("ClientReport").Select
Cells.Select
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 35
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 10
Columns("D").ColumnWidth = 48
Columns("E:E").ColumnWidth = 14
Columns("F:F").ColumnWidth = 5
'End of paste filter Save_Enh into ClientReport
'Start of selecting Enh-Total deleting and than re-creating
Sheets("Enh-Total").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Enh-Total"
'End of selecting Enh-Total deleting and than re-creating
'
'Start of Sub total on Enhancements
Sheets("Save-Enh").Select
Selection.Copy
Sheets("Enh-Total").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 25
Columns("D").ColumnWidth = 67
Columns("E:E").ColumnWidth = 15
Sheets("Enh-Total").Select
Cells.Select
Range("A1").Select
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
'Start of selecting All_Total deleting and than re-creating
Sheets("All_Total").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.DisplayAlerts = False
Sheets.Add
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "All_Total"
'End of selecting All_Total deleting and than re-creating
'Start of sub total for all records
Sheets("Save-All").Select
Selection.Copy
Sheets("All_Total").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 25
Columns("D").ColumnWidth = 67
Columns("E:E").ColumnWidth = 15
Sheets("All_Total").Select
Cells.Select
Range("A1").Select
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
'Replaces #Ref with "All_Total for the formula to work. It gets messed
up in a file rebuild
' This automatically posts the SAP totals to a worksheet
Sheets("Actuals-PIV").Select
Columns("A:A").Select
Selection.Replace What:="#REF", Replacement:="All_Total", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'Clear all contents of worksheet except for the names of the column
fields and the formulas in row 2
Sheets("Fill-Down").Select
Range("A3:F3000").Select
Selection.Clear
'Sheets("Fill-Down").Select
'Cells.Select
'Selection.Copy
'Sheets("FillSave").Select
'Range("A1").Select
'ActiveSheet.Paste
'Columns("A:A").ColumnWidth = 35
'Columns("B:B").ColumnWidth = 11
'Columns("C:C").ColumnWidth = 10
'Columns("D").ColumnWidth = 48
'Columns("E:E").ColumnWidth = 14
'Columns("F:F").ColumnWidth = 5
'Sheets("Fill-Down").Select
'Range("A3:F3000").Select
'Selection.Clear
End Sub