Close one of 2 workbooks open at the end of a macro

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
Here is the code I have wrote. The only thing that I want it to finish with, is that the macro close the worksheet under the "invoice.xls" I just opened up. If you have any other suggestions please let me know.


Sub invoice()

Dim x As String
Dim cell As String
Dim counter As Integer
Dim Invoicenum As String
Dim Name As String
Dim amount As String

Invoicenum = Range("D5")
Name = Range("A10")
amount = Range("d35")


If Name <> "" Then

Workbooks.Open Filename:= _
"C:\PDF FILES\INVOICE TRACKER\Invoice tracker.Xls"
tracker = ActiveWorkbook.Name
Workbooks(tracker).Activate

Range("A2").Activate

For counter = 1 To 200
If "" = ActiveCell.Value Then
ActiveCell.Value = Invoicenum
cell = "A" & ActiveCell.Row
Range(cell).Offset(0, 1).Value = Name
Range(cell).Offset(0, 2).Value = Date
Range(cell).Offset(0, 7).Value = amount
GoTo escape1
Else: cell = "A" & ActiveCell.Row + 1
Range(cell).Activate
End If
Next
escape1:
End If

ActiveWorkbook.Close True

ChDir "C:\PDF FILES\INVOICE TRACKER\INVOICES"

ActiveWorkbook.SaveAs Range("D5")
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Workbooks.Open Filename:= _
"C:\PDF FILES\INVOICE TRACKER\Invoice.Xls"

End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

JPM

Active Member
Joined
Aug 1, 2002
Messages
406
Do I understand you correctly, all you want is to close Invoice.xls? If so just add this line:
Windows("Invoice.xls").Close

JPM
 

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
No, What I want it to do is close the workbook I just saved a "D5", and keep the "Invoice.xls" open
 

JPM

Active Member
Joined
Aug 1, 2002
Messages
406
I see, there are many ways to accomplish that, one of them is to add this line between the last two lines (printing the sheets and open Invoice.xls):

TheFile = ActiveWorkbook.Name

and then add at the end:
Windows(TheFile).Close

JPM
 

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101

ADVERTISEMENT

Yes, That is what I am trying to accomplish, but when I add that in the spot you are telling me to, it comes up with a compile error. Am I doing something wrong?
 

JPM

Active Member
Joined
Aug 1, 2002
Messages
406
I just tried out the macro myself and it worked just fine. The only thing I can think off would be if the directory address (C:\PDF FILES\INVOICE TRACKER\INVOICES) is not correct.

JPM
 

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
Yep, It does work out just fine. I did have one thing placed in the wrong spot. Thanks for the help.
 

Forum statistics

Threads
1,147,844
Messages
5,743,510
Members
423,800
Latest member
IuneKeiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top