E
ed
I have the following code to format a remittance that i
import from my accounting system.
The problems is everytime i run it it opens up another
sheet in the folder that i save the remittances to. I
cannot work out for the life of me why it is doing it.
Please help. Also listed below this macro is the macro i
use to run all of the other macros from.
Sub Format()
Columns("C:F").Select
Selection.Delete Shift:=xlToLeft
Columns("A").Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWindow.SmallScroll Down:=0
Columns("A").EntireColumn.AutoFit
Range("A11").Select
Selection.Font.bold = True
End Sub
Sub bold()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Cells(LR, "A").EntireRow.Font.bold = True
End Sub
Sub Multiple()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
If Cells(LR, "A").Value = Cells(LR - 1, "A").Value Then
Cells(LR, "A").EntireRow.Delete ' Or do you only want to
clear the cell?
End If
End Sub
Sub sum()
Dim Rng As Range
Set Rng = Cells(Rows.Count, "D").End(xlUp)(1, 1)
Rng.Formula = "=-SUM(D1:" & Rng(0, 2).Address(False,
False) & ")"
End Sub
Sub save()
LR = Range("A" & Rows.Count).End(xlUp).Row
ActiveWorkbook.SaveAs "S:\Kingston\FA\Overseas
Payments\Long Haul\2003\Austravel\remittance\" & Cells
(LR, "A").Value & ".xls"
End Sub
__________________________________________________________
Sub Remittance()
Application.Run "'S:\Kingston\FA\Overseas Payments\Long
Haul\2003\Austravel\remittance\remittance.xls'!Format"
Application.Run "'S:\Kingston\FA\Overseas
Payments\Long Haul\2003
\Austravel\remittance\remittance.xls'!Multiple"
Application.Run "'S:\Kingston\FA\Overseas
Payments\Long Haul\2003
\Austravel\remittance\remittance.xls'!Bold"
Application.Run "'S:\Kingston\FA\Overseas
Payments\Long Haul\2003
\Austravel\remittance\remittance.xls'!Save"
Application.Run "'S:\Kingston\FA\Overseas
Payments\Long Haul\2003
\Austravel\remittance\remittance.xls'!sum"
End Sub
import from my accounting system.
The problems is everytime i run it it opens up another
sheet in the folder that i save the remittances to. I
cannot work out for the life of me why it is doing it.
Please help. Also listed below this macro is the macro i
use to run all of the other macros from.
Sub Format()
Columns("C:F").Select
Selection.Delete Shift:=xlToLeft
Columns("A").Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWindow.SmallScroll Down:=0
Columns("A").EntireColumn.AutoFit
Range("A11").Select
Selection.Font.bold = True
End Sub
Sub bold()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Cells(LR, "A").EntireRow.Font.bold = True
End Sub
Sub Multiple()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
If Cells(LR, "A").Value = Cells(LR - 1, "A").Value Then
Cells(LR, "A").EntireRow.Delete ' Or do you only want to
clear the cell?
End If
End Sub
Sub sum()
Dim Rng As Range
Set Rng = Cells(Rows.Count, "D").End(xlUp)(1, 1)
Rng.Formula = "=-SUM(D1:" & Rng(0, 2).Address(False,
False) & ")"
End Sub
Sub save()
LR = Range("A" & Rows.Count).End(xlUp).Row
ActiveWorkbook.SaveAs "S:\Kingston\FA\Overseas
Payments\Long Haul\2003\Austravel\remittance\" & Cells
(LR, "A").Value & ".xls"
End Sub
__________________________________________________________
Sub Remittance()
Application.Run "'S:\Kingston\FA\Overseas Payments\Long
Haul\2003\Austravel\remittance\remittance.xls'!Format"
Application.Run "'S:\Kingston\FA\Overseas
Payments\Long Haul\2003
\Austravel\remittance\remittance.xls'!Multiple"
Application.Run "'S:\Kingston\FA\Overseas
Payments\Long Haul\2003
\Austravel\remittance\remittance.xls'!Bold"
Application.Run "'S:\Kingston\FA\Overseas
Payments\Long Haul\2003
\Austravel\remittance\remittance.xls'!Save"
Application.Run "'S:\Kingston\FA\Overseas
Payments\Long Haul\2003
\Austravel\remittance\remittance.xls'!sum"
End Sub