PDA

View Full Version : Macro crashing excel



yogin
07-25-2012, 07:30 PM
hi guys,

Can you please help me i have got this macro which works fine but it crashing after calculating one or 2 files. Nots sure why??


Private Sub Dr_Disbursement()

'Goes through Dr Name Range
For Each rCell In Sheets("ATTRIBUTES").Range("Dr_Name")

DrName = rCell.Value
rCell.Copy
Sheets("Calculation").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False


'Rebuilt Sheet
Sheets("IRIS-FE").Activate
Application.Run ("TM1REFRESH")
Sheets("PROMED-FE").Activate
Application.Run ("TM1REFRESH")
Sheets("IRIS-DAYS").Activate
Application.Run ("TM1REFRESH")
Sheets("PROMED-DAYS").Activate
Application.Run ("TM1REFRESH")


'Consolidate the data
Sheets("Calculation").Activate
Range("A8:E500").Select
Selection.ClearContents
Range("A7").Select
Selection.Consolidate Sources:=Array( _
"IRIS_FE" _
, _
"Promed_FE" _
), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

Application.CalculateFull


'Copy And Paste Sheets
Sheets(Array("Tax Invoice", "EFT Upload", "Data Input", "Summary", "Calculation")). _
Select
Sheets("Calculation").Activate
Sheets(Array("Tax Invoice", "EFT Upload", "Data Input", "Summary", "Calculation")). _
Copy

ChDir "C:\DR"
ActiveWorkbook.SaveAs Filename:="C:\DR\" & DrName & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Windows(DrName & ".xlsm").Activate



Sheets("Tax Invoice").Select
ActiveSheet.Range("$M$9:$M$335").AutoFilter Field:=1, Criteria1:="1"
Sheets("EFT Upload").Select
ActiveSheet.Range("$R$2:$R$651").AutoFilter Field:=1, Criteria1:="1"
Sheets("Data Input").Select
Rows("1:13").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Range("$K$14:$K$230").AutoFilter Field:=1, Criteria1:="1"
Sheets("Summary").Select
Rows("1:669").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Range("$A$5:$A$905").AutoFilter Field:=1, Criteria1:="1"
Sheets("Calculation").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.MaxChange = 0.001
ActiveWorkbook.PrecisionAsDisplayed = False
Range("B2").Select
ActiveWorkbook.Save
ActiveWindow.Close

Windows("Dr Disburement - All States - All Dr's - Build FileV.xlsm").Activate

Next rCell

End Sub

patel
07-26-2012, 03:05 AM
To find the error run the macro step by step with F8

yogin
07-26-2012, 11:50 PM
i did this and the process ran once,but second time it crashed excel ActiveWindow.Close

patel
07-27-2012, 12:33 AM
comment this line and try again
'ActiveWindow.Close

yogin
07-27-2012, 09:27 PM
i did this it ran for 6 name range and crashed at

ActiveSheet.Range("$K$14:$K$230").AutoFilter Field:=1, Criteria1:="1"

Also i need to close the file as i have about 80 name range i need this macro to run through

Trebor76
07-29-2012, 06:46 PM
For reference, this thread has been cross posted here (http://www.ozgrid.com/forum/showthread.php?t=168060&page=2)