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

New Member
Joined
Jan 18, 2010
Messages
1
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 :)
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

dhosi439

Board Regular
Joined
May 13, 2009
Messages
62
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.

Code:
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

Board Regular
Joined
Jun 28, 2014
Messages
86
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.

Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top