Cereal Spillah
New Member
- Joined
- Mar 30, 2012
- Messages
- 10
Hi!
I'm working in Excel 2010 via Citrix.
I have a sort of long macro that works very well, except when I put application.screenupdating=false at the top of it. For some reason that element seems to make Excel crash when I run my macro. Anyone heard of this or have suggestions for fixing?
Here's my macro below. It basically opens a bunch of other workbooks, copies named ranges and places them in the master workbook, then writes formulas and does some conditional formatting. Again, all of it seems to work great except when I put the screenupdating statement. I've tried placing the screenupdating in all sorts of places, but it seems to crash regardless. Any insight would be appreciated!
I'm working in Excel 2010 via Citrix.
I have a sort of long macro that works very well, except when I put application.screenupdating=false at the top of it. For some reason that element seems to make Excel crash when I run my macro. Anyone heard of this or have suggestions for fixing?
Here's my macro below. It basically opens a bunch of other workbooks, copies named ranges and places them in the master workbook, then writes formulas and does some conditional formatting. Again, all of it seems to work great except when I put the screenupdating statement. I've tried placing the screenupdating in all sorts of places, but it seems to crash regardless. Any insight would be appreciated!
Code:
Sub ConsolFiles()
Application.ScreenUpdating = True
Dim wsMaster As Worksheet
Set wsMaster = Worksheets("Documents")
Dim Files(1 To 46) As String
Files(1) = "004.xlsm"
Files(2) = "005.xlsm"
'file names deleted for brevity...
Files(46) = "301.xlsm"
Dim filepath As String
filepath = "[directory redacted]"
Dim filenum As Integer
With wsMaster
Sheets("Documents").Select
Rows("2:300000").ClearContents
End With
For filenum = 1 To 46
Application.CutCopyMode = False
Workbooks.Open (filepath & Files(filenum)), ReadOnly:=True
Worksheets("BillingDocumentsLog").Range("Records").Copy
Application.Goto wsMaster.Range("FirstEmpty")
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.Goto wsMaster.Range("FirstEmpty")
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
ThisWorkbook.Activate
ActiveWindow.ActivateNext
ActiveWorkbook.Close savechanges:=False
Next
Application.CutCopyMode = False
ThisWorkbook.Activate
Worksheets("Documents").Select
On Error Resume Next
ActiveSheet.Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("A2").Select
'Set Dates for Long Trend
Range("Cn1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("Co1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-7"
Range("co1").Select
Selection.AutoFill Destination:=Range("co1:do1"), Type:=xlFillDefault
Range("co1:dy1").Select
'Designate Currently Missing Files
Range("bf2").Select
ActiveCell.FormulaR1C1 = "=IF(RC25=""Complete"","""",IF(RC[-48]=1,1,""""))"
Range("BF3").Select
Range("BF2").Select
Selection.AutoFill Destination:=Range("bf2:Bs2"), Type:=xlFillDefault
Range("bf2:bs2").Select
'Designate Facility Name, Owner, Age of File, "Region&#"
Range("BU2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-66],Assignments!R2C1:R47C4,4,FALSE)"
Range("BV2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-67],Assignments!R2C1:R47C4,3,FALSE)"
Range("BW2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-50]=""complete"","""",IF(RC[-18]<=1,""0-48 Hours"",IF(RC[-18]<14,""2 Days : 13 Days"",IF(RC[-18]<21,""2 Weeks : 20 Days"",""3 Weeks+""))))"
Range("BW3").Select
Range("BX2").Select
ActiveCell.FormulaR1C1 = "=""Region ""&RC[-68]"
Range("BX3").Select
'Sum Total Outstanding Documents
Range("BY2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-19]:RC[-6])"
Range("BY3").Select
'Set TrendDate-- When was the last day this object was Missing? (If currently missing, default to today)
Range("BZ2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-68]=""NA"","""",RC3+RC[-35])"
Range("BZ2").Select
Selection.AutoFill Destination:=Range("BZ2:CM2"), Type:=xlFillDefault
Range("BZ2:CM2").Select
Range("CN2").Select
ActiveCell.FormulaR1C1 = "=IF(R1C-21>=RC3,COUNTIF(RC78:RC91,"">=""&R1C),0)"
Range("CN2").Select
Selection.AutoFill Destination:=Range("CN2:DO2"), Type:=xlFillDefault
Range("CN2:DO2").Select
'Repeat as "1 week ago, 2 weeks ago..." etc
Range("DP2").Select
ActiveCell.FormulaR1C1 = "=RC[-28]"
Range("DP2").Select
Selection.AutoFill Destination:=Range("DP2:EU2"), Type:=xlFillDefault
Range("DP2:Eq2").Select
'Repeat Claim Type
Range("ER2").Select
ActiveCell.FormulaR1C1 = "=RC[-142]"
'Long Trend - Change this Week
Range("ES2").Select
ActiveCell.FormulaR1C1 = "=RC[-29]-RC[-28]"
Range("ES3").Select
'Short Trend
Range("ET2").Select
Selection.FormulaArray = "=SUM((RC3<=R1C)*(RC78:RC91>=R1C))"
Selection.AutoFill Destination:=Range("ET2:EY2"), Type:=xlFillDefault
Range("ET2:EY2").Select
Range("EZ2").Select
ActiveCell.FormulaR1C1 = "=RC[-6]"
Range("EZ2").Select
Selection.AutoFill Destination:=Range("EZ2:FE2"), Type:=xlFillDefault
Range("EZ2:FE2").Select
Range("FF2").Select
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-5]"
Range("FF3").Select
Range("FG2").Select
ActiveCell.FormulaR1C1 = "=IF(RC25=""Incomplete"",RC24,"""")"
Range("FG3").Select
Range("FG2").Select
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
Range("bf2:fg2").Select
Selection.AutoFill Destination:=Range("bf2:fg" & Cells(Rows.Count, "A").End(xlUp).Row)
Application.CutCopyMode = False
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Cells.Select
ActiveWorkbook.Worksheets("Documents").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Documents").Sort.SortFields.Add Key:=Range("y2:y" & Cells(Rows.Count, "A").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Documents").Sort.SortFields.Add Key:=Range("C2:C" & Cells(Rows.Count, "A").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Documents").Sort
.SetRange Range("A1:fg" & Cells(Rows.Count, "A").End(xlUp).Row)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("X2").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("CN2:Fg2").Select
Selection.NumberFormat = "General"
Cells.Select
Cells.FormatConditions.Delete
With Range("J2")
.FormatConditions.Add Type:=xlExpression, Formula1:="=j2>1"
.FormatConditions(1).NumberFormat = "m/d/yy;@"
.FormatConditions(1).StopIfTrue = True
End With
Range("J2").Select
Selection.Copy
Range("K2:W2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A2:fg2").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($y2=""Incomplete"",$C2<=TODAY()-21)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($y2=""Incomplete"",$C2>TODAY()-21,$C2<=TODAY()-14)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($y2=""Incomplete"",$C2>TODAY()-14,$C2<=TODAY()-2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($y2=""incomplete"",$C2>TODAY()-2)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("X2").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("CN2:Fg2").Select
Selection.NumberFormat = "General"
Range("A2:fg2").Select
Selection.Copy
Application.Goto Range("a2:fg" & Cells(Rows.Count, "A").End(xlUp).Row)
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A2:fg2").Select
Selection.Copy
Application.Goto Range("A2:fg" & Cells(Rows.Count, "A").End(xlUp).Row)
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.UsedRange
Sheets("Preparing").Visible = False
Sheets("Documents").Select
Columns("A:A").NumberFormat = "0"
Range("A1").Select
Application.ScreenUpdating = True
End Sub