Open Workbook at end of macro

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
I have this macro up and running, but it will not open the original workbook back up after I run the macro. This is the function I can not get to finish with (Workbooks.Open Filename:= _
"C:\PDF FILES\INVOICE TRACKER\Invoice.Xls" )
Can anyone help me with this. You have been great with the other posts I have. This forum is great.

Sub Newjob()


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"
Sheets("2005 INVOICES").Select

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.Save
ActiveWorkbook.Close
ChDir "C:\PDF FILES\INVOICE TRACKER\INVOICES"
ActiveWorkbook.SaveAs Range("D5")
ActiveWorkbook.Close
ActiveWorkbook.Save
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True



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

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, Josterbauer,

with this lines you are closing Thisworkbook AND ending the macro

ActiveWorkbook.SaveAs Range("D5")
ActiveWorkbook.Close
In fact you are doing : ThisWorkbook.Close

everything coming after in the code won't be executed !

regards,
Erik
 

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
I changed the end of my macro to look like this, but it will still not open the file named invoice at the end. "HELP" I don't know what I am missing.

ChDir "C:\PDF FILES\INVOICE TRACKER\INVOICES"
ActiveWorkbook.SaveAs Range("D5")
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ThisWorkbook.Close





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

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Is there a misunderstanding?
Perhaps i don't see what you want or the inverse?

when you Close the workbook the macro stops
a macro can not run when the "parent-workbook" is closed
try this
Code:
Sub a()
Msgbox "I'm open"
ThisWorkbook.Close True (True = Saving the workbook)
Msgbox "I'm closed"
End Sub
what did you see? (only one Msgbox)
so don't put anything after ThisWorkbook.Close

kind regards,
Erik
 

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101

ADVERTISEMENT

I see what you are saying know. Here is what I am trying to do. I have an invoice sheet that I start out in, and hit the macro button and it runs and transfers data to a different sheet, saves the invoice in the invoice folder, and prints the invoice that I just saved. I want it to return to the original sheet that I started in, so I can do more than one invoice at a time. If I take out the (This workbook.close) it keeps the invoice that I just saved to the invoice floder, and the original sheet I started on. If I can close the invoice that I just saved, it would be all I need it to do.See if this info helps.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
To avoid every confusion it's good to give your workbooks a name within the code like this:
Code:
Sub make_new_file_of_invoice_dir_macro()
file_name = ActiveWorkbook.Name
....
create_new_workbook_for_invoice
paste_invoice_in_new_workbook
copy_page_setup
send_invoice
....
End Sub
Code:
Sub create_new_workbook_for_invoice()
new_file_name = Range("invoice_nr") & " " & Range("client_number") Application.SheetsInNewWorkbook = 1
Workbooks.Add
new_file_fullname = ThisWorkbook.Path & "\" & new_file_name
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs new_file_fullname
Application.DisplayAlerts = True
ActiveSheet.Name = "invoice"
End Sub
or you can treat the workbooks as Object (I've no example: never used it that way)

this could be a step in the good direction ...
I would suggest to adapt your code and then post back

kind regards,
Erik
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

sorry,
I forgot to add how to call this workbooks
example:
Workbooks(file_name).Activate

regards,
Erik
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
As response to your PM how to apply this:

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


Each time you need to do something with this one you code
Wotkbooks(tracker).Activate

I suggest you start up with a little macro first to see what happens and then expand step by step.

Click in the code and hit F8 to pass through line by line and look on your sheets what happens

kind regards,
Erik
 

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
I'll put that into the macro, and play around with it. Thanks for your help so far. I will probobly be asking you a few more questions to get this code wrote. I will post what I have worked up later.
 

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
Here is what I came up with so far. I just need to close the sheet that is under the "invoice.xls" that gets opened at the end of the code

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
 

Forum statistics

Threads
1,147,679
Messages
5,742,575
Members
423,738
Latest member
AshleyKitsune

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