Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > 10 seconds to open excel workbook w/macros
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Bud Bud is offline
external usenet poster
  
Posts: 61
Default 10 seconds to open excel workbook w/macros
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: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: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: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: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: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: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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 30
Default 10 seconds to open excel workbook w/macros
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: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: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: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: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: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: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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Workbook with Macros Norman Jones Excel Programming 0 January 2nd 07 08:04 PM
Open workbook using VBA but don't run macros Paulymon Excel Programming 1 October 10th 06 04:12 AM
Open workbook without running Macros Raul[_2_] Excel Programming 2 May 27th 06 02:51 PM
Open workbook-macros enabled, opening another with macros George J Excel Programming 5 September 17th 04 02:07 PM
Excel crashes when attempting to open workbook...even with macros disabled llowwelll[_10_] Excel Programming 1 May 24th 04 09:38 PM


All times are GMT +1. The time now is 06:26 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement