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. |
Help with macro
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 |
Help with macro
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 |
Help with macro
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 |
Help with macro
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 |
Help with macro
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 |
Help with macro
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 |
Help with macro
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 |
Help with macro
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" |
Help with macro
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. |
Help with macro
Theslaz, please post in the forum as you can benefit from the interface better. The attached file worked fine for me, although i do not have a fax printer, i simply print out to my default printer and a csv backup is made in T:\Fax Backup. +-------------------------------------------------------------------+ |Filename: Master Invoice 2008-09.xls | |Download: http://www.thecodecage.com/attachment.php?attachmentid=25| +-------------------------------------------------------------------+ -- 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 |
Help with macro
I think that I have fixed the printing problem. I placed the code for the printer at the beginning of the macro and it appears to be working. ActiveSheet.PrintOut 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 End Sub -- 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 |
Help with macro
Check out the latest attachment i made as it has the save code there! Newsgroups don't cope with the functionality of forum boards very well which is why i asked you to post here! -- 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 |
Help with macro
Sorry for the double post! Some reason or another the original posts didn't show. Got your file and everything is working perfect. Couldn't ask for anything better. Thanks for your trouble. -- 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 |
Help with macro
Glad we could be of help! If your query has been solved please take a moment to let us and eveyone else know by going to Thread ToolsMark Thread Solved,shown by this icon27 +-------------------------------------------------------------------+ |Filename: marksolved.gif | |Download: http://www.thecodecage.com/attachment.php?attachmentid=27| +-------------------------------------------------------------------+ -- 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 |
Help with macro
Simon Lloyd;127009 Wrote: Glad we could be of help! If your query has been solved please take a moment to let us and eveyone else know by going to Thread ToolsMark Thread Solved,shown by this icon27 Went to "Thread Tools" and it only gave three choices: Show Printable Version; Email This Page and Unsubscribe from this thread! -- 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 |
[SOLVED]: Help with macro
Ah yes, my fault, its because you continued the thread from the one imported from the newsgroups. No worry i've done it! Thanks for that. -- 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 |
All times are GMT +1. The time now is 03:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com