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

Hi Bub,
To find the lower right corner that gets saved on a sheet I use Ctrl + End.
If a cell is formated and contains nothing in it, Excel has to save the
formating of that cell even though it's blank. Clear empty rows by deleting
the rows and saving the file.

Let me know if that's your issue.

"Bud" wrote:

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