Code required to save current summary sheet

[MooSki]

Hi all,

First of all, I hope this is in the correct forum and that you can assist with a semi-urgent query.

I have been given an MS Excel 2003 spreadsheet from a customer which has two sheets to it, one is the raw data (taken from their database) the second is a summary sheet showing financial totals.

Within the summary sheet is a drop down box (cell B6, FYI) listing all of the account holders within the data. Once an account holder is chosen, the data within a couple of tables on that summary sheet is updated using VLOOKUP and this is all fine.

What we would like to do is have a button that once pressed, takes that Summary sheet with the account holder information and saves it to a new Excel file and closing that file immediately. The file name needs to include the name of the account holder, held within cell B6 as detailed above. However, we can't have the raw data included in this new file and we also need to disable any drop down boxes, buttons, macros, etc.

Is this possible? And if so, is there any information you would require to assist me?

Kind regards,
Mooski.

 

[firefytr]

Hi Mooski,

What kind of drop down is it? Data Validation? Is it a "in-cell" drop down? Is there a specific location you would like the file saved to? What about past files? Would you like to overwrite past data? Save with the date in the filename?

-----------
Regards,
Zack Barresse

 

[MooSki]

Hi firefytr,

It's an in-cell dro pdown based on a column on the afore-mentioned data sheet.

There would be a specific location to save the files to, but I'm not sure what it is at the moment, let's say "C:\Saved Files\" for now.

We wouldn't be bothered about over-writing files, these summaries are based on a rolling 12 months basis and taht is all that is required.

There will be no need for a date stamp on the filename, but there will need to be the "customer" name that will be on the in-cell drop down list.

Many thanks,
Mooski.

 

[xlbo]

Have a look at this FAQ: faq707-2482 How to save a sheet seperately from the main file

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Maybe this would work for you...

Sub CopySheetToNewBookPlease()
    Dim wb As Workbook, ws As Worksheet, c As Range
    ThisWorkbook.Sheets(2).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set ws = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set c = Selection
    ws.Cells.Copy
    ws.Cells.PasteSpecial xlPasteValues
    ws.Range("B6").Validation.Delete
    ws.Copy
    Set wb = ActiveWorkbook
    wb.SaveAs "C:\" & wb.Sheets(1).Range("B6").Value & " " & Format(Date, "m-d-yyyy") & ".xls"
    wb.Close False
    c.Select
    Application.CutCopyMode = False
End Sub

Let us know if this works for you or not.

-----------
Regards,
Zack Barresse