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]

OK,
I cleaned it up for you.
It still could be better.

some comments:
check your Dim statements since i deleted a part and variables were added (tracker, invoice, cell?)
Workbooks(tracker).Activate : you did't need this line here, since the workbook which was just opened is already active (i just gave you that as an example)
For counter = 1 To 200: are you trying to find the first empty cell in the column? ==> this is one of the most asked questions here
You don't have to make a loop for that but you can find that at once.
Range("A65536").End(xlup).Offset(1, 0)
instead of escape use exit for
Workbooks(invoice).Close
this was your main question

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
invoice = ThisWorkbook.Name
'Workbooks(tracker).Activate you don't need this line here workbook which was just opened is already active

Range("A2").Activate

For counter = 1 To 200
  If "" = ActiveCell.Value Then
  ActiveCell.Value = Invoicenum
    With Cells(ActiveCell.Row, 1) 'but this IS the active cell, so you could replace by ActiveCell
    .Offset(0, 1).Value = Name
    .Offset(0, 2).Value = Date
    .Offset(0, 7).Value = amount
    End With
  Exit For 'goto escape
  Else:
  Cells(ActiveCell.Row + 1, 1).Activate 'and this by ActiveCell.Offset(1, 0)  End If
Next counter
End If

Workbooks(tracker).Close True

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

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

Workbooks.Open Filename:="C:\PDF FILES\INVOICE TRACKER\Invoice.Xls"
Workbooks(invoice).Close

End Sub

I'll arrange your loop in a few minutes...

regards,
Erik

 

[JOSTERBAUER]

I see what you have done. When the code hits this point
invoice = ThisWorkbook.Name

it comes up with a compiling error.
What do I need to fix this?


If I am looking at this right, you are trying to name the workbook that is running as invoice, and that will further down in the code be saved as "D5"? After you have it named, you can then tell it to close correct?

 

[erik.van.geit]

few minutes interrupted by emails :wink:

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
Dim invoice 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
invoice = ThisWorkbook.Name

Range("A2").Activate

  With Range("A65536").End(xlUp).Offset(1, 0)
    .Value = Invoicenum
    .Offset(0, 1).Value = Name
    .Offset(0, 2).Value = Date
    .Offset(0, 7).Value = amount
  End With
End If
Workbooks(tracker).Close True

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

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

Workbooks.Open Filename:="C:\PDF FILES\INVOICE TRACKER\Invoice.Xls"
Workbooks(invoice).Close True

End Sub

does it work for you?

kind regards,
Erik

 

[erik.van.geit]

posting at the same time

You're right

After you have it named, you can then tell it to close correct?

this is a mistake
change the code
Workbooks(invoice) ==> ThisWorkbook
OR if you want to elaborate your macro (so this seems safer to me)
after this line
Workbooks(invoice).SaveAs Range("D5")
put
invoice = ActiveWorkbook.Name

we are getting close,
Erik

EDIT: your first question ==> I warned you to check your Dim statements
Dim invoice As String (already added in this version)

 

[JOSTERBAUER]

Yes, and no. Everything ran through just fine, no compile error. There was a few things that didn't happen. It didn't drop this information

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

To the "invoice tracker" worksheet.

The other thing that happened was it kept the sheet that was saved as "D5" and that was named (invoice), and closed this file instead
Workbooks.Open Filename:="C:\PDF FILES\INVOICE TRACKER\Invoice.Xls"
What do I need to do to fix it? Thanks you have been a great help!!! It's great to have someone like you in this forum.

 

[JOSTERBAUER]

Did you just want me to add this exact fix

Workbooks(invoice)==>ThisWorkbook

on top of what you already have done? When I do so, it comes up with a syntax error, or did you need me to remove anything you added to the code?

 

[erik.van.geit]

1. Put on a paper what your macro should do in your own words.
Make perhaps some little drawings or a scheme.
"this workbook will open, here data will be dropped, this will close, ..."
2. Put your VBA Window aside to the right so you can see your Excel. Click in the code.
3. Then go for a little walk and think at beautiful things = very important!
4. Step through your code hitting F8 line by line.
See what it does and compare to what you expect.

regards,
Erik

 

[erik.van.geit]

Did you just want me to add this exact fix

Workbooks(invoice)==>ThisWorkbook

change every occurence of Workbooks(invoice) into ThisWorkbook (you can use Ctrl H having "Workbooks(invoice)" selected)
but I would prefer the second solution (see: Tue Jan 18, 2005 9:50 pm)being aware of later changes

you're close, so close,
Erik

 

[JOSTERBAUER]

Everything is working "GEAT" !!!!!! I did have one problem with it dropping the information in the invoice tracker worksheet, but later found out why I couldn't see it. I had A201 with the word yearly totals. I just moved it to a different column, and know it is working great. Thank you very much.

 

[erik.van.geit]

I'm tired now zzzzzz (we should have an emoticon for that)
some latest comments

you can delete this unnecessary line
Range("A2").Activate

change those lines
End If
Workbooks(tracker).Close True
TO
Workbooks(tracker).Close True
End If
(If Name <> "" Then : if no name is found then you can't close "tracker" since it will not be opened)

avoid using terms like Name, Item, Time, since they are used (needed) by VBA itself: that's why the first character changes in a upercase automatically!

sleeping,
but enjoying your emotions
Erik