Macro to automate : a.) Saving of Open Worksheet as new Excel file, b.)Print command to print 2 copies each with different footer, and some more queri

[sailee]

All for the same file
a.) I have an invoice template in one excel file in which I fill in data, out of which some of it auto fills as it is linked to another excel file in which the master data is stored and updated. I wish to edit this invoice "template" (if u may call it that) and once I am done, I wish to click on the Print Macro enabled button that will print 2 copies. (i) 1st Copy with "Customer Copy" and (ii) 2nd Copy with "Merchant Copy" inserted at the bottom
b.)Then I would like to have another Macro enabled button which would:
-Save existing open Worksheet(which has just been printed) as a new Excel file with only the values (no Formalae nor Linking) , retain the exact formatting in the new file (column width etc.), and derive the filename from a cell value from the existing open sheet(edited template) [which will not be present in the new file as it will be out of print area] (the cell is on the far right on the sheet), Close the new file created to a designated folder, and keep the "template" open. Template should be open with the last edited info in order to key in the next chronological invoice no. (Don't know how to automate Invoice Numbers in this scenario, incrementation of the Invoice no. upon the event of Save as New file is daunting!)
c.)I also would like to be able to display numbers in Number Format, but as 001, or 01, or 00009 if that is the way I want to display it. For some reason, in Number format, it will display these values as 1,1 and 9 resp. How do I do that?
d.)Autocomplete! I know I know everyone seems to ask this same question but seriously is it possible? I have drop downs for Names and Products, and one has to keep manually scrolling down, is there a way to atleast be able to put in the first letter and have all records starting with the 1st letter displayed? Or is using the combobox with setting the property to autocomplete the only solution? But I wasn't completely able to do that either!
e.) Well I 've been able to achieve everything in bits and pieces (with my limited VBA knowledge) , but not been able to make all the functionalities work together at once!
Any help would be highly highly appreciated!
Thanks a million for giving it a read atleast ! ( I know I wrote a lot)
Warm Regards,
Sailee

 

[dhosi439]

answer to a)
This is a macro assigned to a form control button, not an activex button.
Insert a Button form control, click where in the worksheet you want it. You will be prompted to assign a macro, click new. Copy and paste the code below into the Sub.

With Worksheets("Simple Invoice")
    .PageSetup.PrintHeadings = False
    .PageSetup.PrintGridlines = False
    .PageSetup.PrintArea = "$A$1:$C$42"
    .Range("A41").Value = "Customer Copy"
    .Range("A41").Font.Bold = True
    .Range("A41").Font.Size = 12
    .PrintOut
End With

With Worksheets("Simple Invoice")
    .PageSetup.PrintHeadings = False
    .PageSetup.PrintGridlines = False
    .PageSetup.PrintArea = "$A$1:$C$42"
    .Range("A41").Value = "Merchant Copy"
    .Range("A41").Font.Bold = True
    .Range("A41").Font.Size = 12
    .PrintOut
End With

Answer to b)
I am actually in the process of figuring this out. If I get something working I will post it.

Answer to c)
You need to make a custom number format, which contains however many leading zeros you want to have.

Shown here: http://office.microsoft.com/en-us/excel/HA102748231033.aspx

Answer to d)
The only way I know to do this is to create combo boxes using named ranges. This is easiest using userforms. As you will not clutter your worksheet.

Here is a link to another thread where I show code on how to do this using a table and a user form: http://www.mrexcel.com/forum/showthread.php?t=442037

Good luck.

 

[jimmmi]

answer to a)
This is a macro assigned to a form control button, not an activex button.
Insert a Button form control, click where in the worksheet you want it. You will be prompted to assign a macro, click new. Copy and paste the code below into the Sub.

With Worksheets("Simple Invoice")
    .PageSetup.PrintHeadings = False
    .PageSetup.PrintGridlines = False
    .PageSetup.PrintArea = "$A$1:$C$42"
    .Range("A41").Value = "Customer Copy"
    .Range("A41").Font.Bold = True
    .Range("A41").Font.Size = 12
    .PrintOut
End With

With Worksheets("Simple Invoice")
    .PageSetup.PrintHeadings = False
    .PageSetup.PrintGridlines = False
    .PageSetup.PrintArea = "$A$1:$C$42"
    .Range("A41").Value = "Merchant Copy"
    .Range("A41").Font.Bold = True
    .Range("A41").Font.Size = 12
    .PrintOut
End With

Answer to b)
I am actually in the process of figuring this out. If I get something working I will post it.

Answer to c)
You need to make a custom number format, which contains however many leading zeros you want to have.

Shown here: http://office.microsoft.com/en-us/excel/HA102748231033.aspx

Answer to d)
The only way I know to do this is to create combo boxes using named ranges. This is easiest using userforms. As you will not clutter your worksheet.

Here is a link to another thread where I show code on how to do this using a table and a user form: data validation/combo box for long list of items

Good luck.

Its been 4 years, but have you managed to fix this?