|Thanks again. I've done some more work on it, so I have a better grasp on what I still need to accomplish - thanks for letting me get back to you!|
To review so you don't have to scroll up (and some of that is now innacurate anyway), here's what I'm up to:
I have an excel sale receipt worksheet which draws data from an excel data worksheet in the same workbook. The sale receipt macro is running perfectly, and is set up to allow for different versions of excel (nice!) The macro prompts for user input to save the "whole workbook" as a suggested filename (based upon a cell's contents), and also allows the user to specify a directory, and then close the program.
I would actually like to save only the worksheet "Sales Receipt" instead of the entire Workbook. The original "Sales Receipt" worksheet needs to be left unchanged and unsaved so it is ready for the next sale.
My workbook is now called "2012 Ledger", and it contains the following eleven (11) Worksheets:
2. Incoming Receipt
3. Incoming Summary
4. Sales Receipt
5. Sales Summary
6. Personal Product Receipt
7. Personal Product Summary
9. Misc Income & Expenses
11. Add New Product
The end result I am going for, is:
A. To be able to enter data into sheets 2, 4, and 6 (for incoming product, outgoing sales, and personally used product repectively).
B. To have that data put into the next available colum on sheets 3, 5, and 7 respectively. Column A in sheets 3, 5, and 7 contains the product code. Column B has the description of the product. A and B are already in by way of reference to the Worsheet "Inventory" (=Inventory!A4 etc etc.). For each time the "Sales Receipt" is filled in, the cells labeled Date, Subtotal, Taxes, and the amount of each product are entered in the respective cells in the next available column. (You were correct in observing that I don't need the product put in, as it is already in Column 1.)
C. As Worksheets 3, 5, and 7 are filled with data, the contents of those sheets are to be referred into the Worksheet "Inventory", where it then is added to stock, subtracted from stock, and totals given.
D. The Worksheet "Inventory" is also where the product numbers, product descriptions, prices, taxes, COGS, shipping, etc etc are stored.
E. As for the first post I had above, the 'M.A. Sales to Customers' sheet no longer exists, it is now the "Sales Summary". The first order in is column C, with the date in C2, Subtotal in C3, Taxes in C4, and total in C5. Below that, the first product is in C9 and the last product is in C1587.
F. All of the resulting data from Worksheets 1-9 are chanelled into Worksheet 10, which is basically a Schedule C for tax time.
G. Sheet 11 is yet for me to tackle later... it is a form to be used to easily enter new products into the "Inventory" worksheet.
Wow - I can't believe what I've gotten myself into here - easy for you perhaps - but I feel like such a newby!!
Obviously this is easier seen than described, so if you want I can send you the whole Workbook if you like - it would be a lot simpler I imagine. Here is the script I am starting with - posted correctly this time - thanks for the input on how to post code!
Dim wb As Workbook
Dim NewFileName As String
Dim NewFileFilter As String
Dim myTitle As String
Dim FileSaveName As Variant
Dim NewFileFormat As Long
Set wb = ThisWorkbook
If Application.Version >= 12 Then
NewFileName = wb.Sheets("Sales Receipt").Range("I6").Value & ".xlsm"
NewFileFilter = "Excel Macro-Enabled workbook (*.xlsm), *.xlsm"
NewFileFormat = 52
NewFileName = wb.Sheets("Sales Receipt").Range("I6").Value & ".xls"
NewFileFilter = "Microsoft Excel Workbook (*.xls), *.xls"
NewFileFormat = xlNormal
myTitle = "Navigate to the required folder"
FileSaveName = Application.GetSaveAsFilename _
If Not FileSaveName = False Then
wb.SaveAs Filename:=FileSaveName, _
MsgBox "File NOT Saved. User cancelled the Save."