M
mathel
Hi,
I am working with Excel 2003 and have a problem with ‘End If’ statement in a
macro. I have created a workbook named ‘Agency Billing. What I need to do
is:
-if the sum of range D246 = 0.00, then go to sub-routine called ‘CopyCosts’
-otherwise, copy specified range, open wb Bad Debt, find next blank row,
paste data, save & close Bad Debt wb, then
-go to sub-routine ‘CopyCost’
-if cell named ‘exp’ = 0.00, the go to sub-routine to CloseFile, otherwise
-copy range named ‘CopyCost’, open wb Collection Cost, find next blank row,
paste data, save and close Collection Cost wb, then
-close active wb (Agency Billing) without saving.
What happens is if D246 is greater than 0.00, it will run this routine,
but stops without going to the next sub-routine to CopyCosts. I have no
idea why it will not run and am hoping someone can help. A portion of the
macro I have is as follows:
If Application.Sum(Worksheets("Bad Debt").Range("D246")) = 0 Then
Call CopyCost
Else
End If
Dim lastRow As Long
lastRow = Cells(Rows.Count, "l").End(xlUp).Row
'set variable to the last used row in L
Range("H1:L" & lastRow).Copy
Workbooks.Open Filename:="G:\Bad Debt"
Range("A5").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
Sub CopyCost()
Application.Goto Reference:="exp"
If Worksheets("Input").Range("exp") = 0 Then
Call CloseFile
End
Else
End If
Application.Goto Reference:="CopyCost"
Selection.Copy
Workbooks.Open Filename:="G:\Collection Costs"
Range("A5").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Select
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
Sub CloseFile()
Sheets("Input").Select
Range("A1").Select
MsgBox "Collection Costs & Bad Debt have been recorded. This file will
close now"
Application.ScreenUpdating = True
ActiveWorkbook.Close SaveChanges:=False
End Sub
Thanks
I am working with Excel 2003 and have a problem with ‘End If’ statement in a
macro. I have created a workbook named ‘Agency Billing. What I need to do
is:
-if the sum of range D246 = 0.00, then go to sub-routine called ‘CopyCosts’
-otherwise, copy specified range, open wb Bad Debt, find next blank row,
paste data, save & close Bad Debt wb, then
-go to sub-routine ‘CopyCost’
-if cell named ‘exp’ = 0.00, the go to sub-routine to CloseFile, otherwise
-copy range named ‘CopyCost’, open wb Collection Cost, find next blank row,
paste data, save and close Collection Cost wb, then
-close active wb (Agency Billing) without saving.
What happens is if D246 is greater than 0.00, it will run this routine,
but stops without going to the next sub-routine to CopyCosts. I have no
idea why it will not run and am hoping someone can help. A portion of the
macro I have is as follows:
If Application.Sum(Worksheets("Bad Debt").Range("D246")) = 0 Then
Call CopyCost
Else
End If
Dim lastRow As Long
lastRow = Cells(Rows.Count, "l").End(xlUp).Row
'set variable to the last used row in L
Range("H1:L" & lastRow).Copy
Workbooks.Open Filename:="G:\Bad Debt"
Range("A5").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
Sub CopyCost()
Application.Goto Reference:="exp"
If Worksheets("Input").Range("exp") = 0 Then
Call CloseFile
End
Else
End If
Application.Goto Reference:="CopyCost"
Selection.Copy
Workbooks.Open Filename:="G:\Collection Costs"
Range("A5").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Select
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
Sub CloseFile()
Sheets("Input").Select
Range("A1").Select
MsgBox "Collection Costs & Bad Debt have been recorded. This file will
close now"
Application.ScreenUpdating = True
ActiveWorkbook.Close SaveChanges:=False
End Sub
Thanks