• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro copies to create new worksheets. Worksheets open slow!

t33p33

New Member
I have a macro that is design to import data from a CSV file into a formatted spreadsheet. A copy of the sheet is then created and a filter applied based upon a name in a column. Three sheets are created to this way, each with info filtered to only display a particular person's rows of data.

When I open the worksheet and attempt to switch between sheets it is VERY slow! Any ideas of how to speed this up?

Below is the macro

Code:
Sub createReport()
importData
formatCombined
createSSMReports
Complete
End Sub
Sub importData()
'Import Data and create Combined Report
Application.ScreenUpdating = False

  Sheets("Combined Report").Visible = True
  Sheets("Combined Report").Select
 
'Import Data
Dim WB As Workbook
Set WB = ThisWorkbook
Dim fn
Dim wbFrom As Workbook
  
 On Error Resume Next
  fn = Application.GetOpenFilename
 
  Workbooks.Open fn
  Set wbFrom = ActiveWorkbook
  
  
  
  Set wbFrom = Nothing
Range("A1:S800").Select
  Selection.Copy
wbFrom.Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=False

WB.Activate
  Sheets("Combined Report").Select
  Range("Table1[Day Updated]").Select
  Range("A2").Select
  ActiveSheet.Paste
 
ThisWorkbook.Worksheets.Select
  Sheets("Combined Report").Select
  Range("Table1[Day Updated]").Select
  Range("A2:AS2").Select
  Application.CutCopyMode = False
  Selection.ListObject.ListRows(1).Delete
End Sub
Sub formatCombined()
  Columns("A:S").Select
  Columns("A:S").EntireColumn.AutoFit
  'Columns("D:D").Select
  'ActiveWindow.FreezePanes = True
  Range("Table14[[#Headers],[Overall Project Status]:[Timeline Risk]]").Select
  Selection.ListObject.ListColumns(5).Delete
  Selection.ListObject.ListColumns(5).Delete
  Selection.ListObject.ListColumns(5).Delete
  Selection.ListObject.ListColumns(5).Delete
  Selection.ListObject.ListColumns(5).Delete
End Sub
Sub createSSMReports()
  
  'SSM1
  ActiveSheet.ListObjects("Table14").Range.AutoFilter Field:=6, Criteria1:= _
  "SSM1"
  Sheets("Combined Report").Select
  Sheets("Combined Report").Copy Before:=Sheets(3)
  Sheets("Combined Report (2)").Select
  Sheets("Combined Report (2)").Name = "SSM1"
  
  'SSM2
  Sheets("Combined Report").Select
  ActiveSheet.ListObjects("Table14").Range.AutoFilter Field:=6, Criteria1:= _
  "SSM2"
  Sheets("Combined Report").Select
  Sheets("Combined Report").Copy Before:=Sheets(4)
  Sheets("Combined Report (2)").Select
  Sheets("Combined Report (2)").Name = "SSM2"
  
  'SSM3
  Sheets("Combined Report").Select
  ActiveSheet.ListObjects("Table14").Range.AutoFilter Field:=6, Criteria1:= _
  "SSM3"
  Sheets("Combined Report").Select
  Sheets("Combined Report").Copy Before:=Sheets(5)
  Sheets("Combined Report (2)").Select
  Sheets("Combined Report (2)").Name = "SSM3"
  Sheets("Combined Report").Select
  ActiveSheet.ListObjects("Table14").Range.AutoFilter Field:=6

End Sub
Sub Complete()

Sheets("Import New Data").Visible = False
Application.ScreenUpdating = True
Sheets("Combined Report").Select
MsgBox "SUCCESS! Data has imported and report has been created.", vbOKOnly, "Report Successfully Created"

End Sub
 
I would first try adding the lines
Code:
Application.DisplayAlerts = True
Application.Screenupdating = True
to all the subroutines that disable Display Alerts
 
Unfortunately I still have the same issue. It runs slowly and anytime that I reopen the spreadsheet.
 
Unfortunately I still have the same issue. It runs slowly and anytime that I reopen the spreadsheet.


I have run into that issue on occasion and have found that some data has been inadvertently posted in columns where it vastly increases the worksheet size, or corrupts the file completely. Now I just run a quick erase macro to clear all the possible cells that I know shouldn't hold data and the files start to behave much more quickly.
 
Back
Top