Help with macro

I have a workbook with 5 pages ( I apologize if those are not the right
terms )

1 for "Reference tables"
1 for "Invoice data Entry"
1 for "Job Pending & Completion List"
1 for "Print Invoice"
1 for "Summary Statistics"

In the Print "Invoice page" it shows all the necessary information that
I need for the invoice retrieved from the "Invoice Data Entry" page.

What I would like to have is a button on my "Print Invoice" page that
would print the invoice ( print to fax ) and at the same time enter the
date under a column for "Date Sent" in the "Job Pending & Completion
List" page.

It appears what I have do do in my macro is to retrieve the Invoice
number from the "Print Invoice" page and search for that number in the
"Job Pending & Completion List" and then insert the proper date under
the "Date Sent" column in the row for that invoice number.

I have been entering this date manually; but all to often I forget and
than I wind up resending the fax. Employer at other end has no sense of
humor and gets very annoyed! Trying to eliminate that.

Theslaz, join our forums where you can attach a workbook where we would
be glad to resolve this issue for you.

It would be difficult to solve here as you need to let us know the
range the invoice number appears on, the range the date appears on, the
range the completed date should go and if you want the job status to
change after printing we also need to know the range that appears in not
to mention the sheets for all the above.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34925

I am now a member. Let me know what you require from me and I will send
it.

Thanks in advance


--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34925

Welcome to The Code CageOn your next post
before submitting it scroll down a little further where you will see a
Manage Attachment button click this and upload your workbook and we'll
take it from there.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34925

Here is the file you requested. Need more info; just ask!


+-------------------------------------------------------------------+
|Filename: Master Invoice 2008-09.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=24|
+-------------------------------------------------------------------+

--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34925

Add a command button to your worksheet and add this code, i'm not too
sure on the fax as everyone's is different but the rest of the code will
work fine!


Code:
--------------------

Dim fRange As String
fRange = Sheets("Job Pending & Completion List").Cells.Find(What:=Sheets("Print Invoice").Range("I2").Value, _
After:=Sheets("Job Pending & Completion List").Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
With Sheets("Job Pending & Completion List")
.Range(fRange).Offset(0, 7).Value = Date
End With
Application.ActivePrinter = "microsoft fax"
ActiveSheet.PrintOut


--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34925

I have added a Command button and inserted the code. You are right about
the Fax as I keep getting an error. What I have on my computer is a HP
OfficeJet J5700 series All-In-One printer. When I want to fax; I just
tell it to use the OfficeJet 5700 series Fax.

I am using XP Home Edition; Version 2002; Service Pack 3


--
theslaz
------------------------------------------------------------------------
theslaz's Profile: http://www.thecodecage.com/forumz/member.php?userid=44
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34925

Like i said i'm not sure about your fax and i don't know the correct
name for that however if you set your default printer to be the fax you
can use this:


Code:
--------------------

Dim fRange As String
fRange = Sheets("Job Pending & Completion List").Cells.Find(What:=Sheets("Print Invoice").Range("I2").Value, _
After:=Sheets("Job Pending & Completion List").Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
With Sheets("Job Pending & Completion List")
.Range(fRange).Offset(0, 7).Value = Date
End With
ActiveSheet.PrintOut

--------------------
Did the rest of the code work for you?


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=34925

Simon Lloyd wrote:
Like i said i'm not sure about your fax and i don't know the correct
name for that however if you set your default printer to be the fax you
can use this:


Code:
--------------------

Dim fRange As String
fRange = Sheets("Job Pending & Completion List").Cells.Find(What:=Sheets("Print Invoice").Range("I2").Value, _
After:=Sheets("Job Pending & Completion List").Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
With Sheets("Job Pending & Completion List")
.Range(fRange).Offset(0, 7).Value = Date
End With
ActiveSheet.PrintOut

--------------------
Did the rest of the code work for you?


That worked. I will set the Fax printer as the "Default Printer"

I thank you.

Are you up for one more problem/item I would like to setup?

The HP printer that I use does not keep a copy of the actual fax that I
have sent. ( an older model Hp printer did and I used that feature a lot
) What I would like is to have a file generated showing only the
actual invoice and saved on my computer. I would like that file to be
named using the Invoice number and saved as a .CSV file. The file would
be saved to my "T" drive which is an external drive that I use for
backups. I could name the directory "Fax Backups"

Theslaz wrote:
Simon Lloyd wrote:
Like i said i'm not sure about your fax and i don't know the correct
name for that however if you set your default printer to be the fax you
can use this:


Code:
--------------------
Dim fRange As String
fRange = Sheets("Job Pending & Completion
List").Cells.Find(What:=Sheets("Print Invoice").Range("I2").Value, _
After:=Sheets("Job Pending & Completion List").Range("A1"),
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Address
With Sheets("Job Pending & Completion List")
.Range(fRange).Offset(0, 7).Value = Date
End With
ActiveSheet.PrintOut
--------------------
Did the rest of the code work for you?


That worked. I will set the Fax printer as the "Default Printer"

I thank you.

Are you up for one more problem/item I would like to setup?

The HP printer that I use does not keep a copy of the actual fax that I
have sent. ( an older model Hp printer did and I used that feature a lot
) What I would like is to have a file generated showing only the actual
invoice and saved on my computer. I would like that file to be named
using the Invoice number and saved as a .CSV file. The file would be
saved to my "T" drive which is an external drive that I use for backups.
I could name the directory "Fax Backups"
Problem. When I run the code; the Excel program hangs. It just sits at
the "Printing" dialog box and nothing happens. I tried to print the
invoice without using the code; and it printed no problem. I then tried
via the code and it hangs.