Open Workbook at end of macro

[JOSTERBAUER]

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

 

[erik.van.geit]

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]

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]

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

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]

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]

To avoid every confusion it's good to give your workbooks a name within the code like this:

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

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]

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

regards,
Erik

 

[erik.van.geit]

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]

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]

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