Saving macro generated reports

Resolved · Low Priority · Version 2003

Fri 22 Jan 2010, 16:08 Delegate Simon said...

Simon has attended:
Excel VBA Intro Intermediate course

Saving macro generated reports

Can you show me the code for saving the report in a new file / folder rather than in the data worksheet. Thanks

Sun 24 Jan 2010, 19:39 Trainer Anthony said...

RE: Saving macro generated reports

Hi Simon, thanks for the query. Here's the code you need. Make sure the mynewfilename variable is set your desktop (it's set to mine in the example below). The code copies strName to a new file, then saves that file to the desktop.

***

Dim mynewfilename As String

mynewfilename = "C:\Documents and Settings\User10\Desktop\" & strName & ".xls"

Sheets(strName).Select
Sheets(strName).Copy
ActiveWorkbook.SaveAs Filename:=mynewfilename

ActiveWorkbook.Close

***

Hope this is useful!

Anthony

Sun 31 Jan 2010: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Use the SUBTOTAL function in Excel

You can create subtotals in your spreadsheet using the SUBTOTAL function, which looks like this:

=SUBTOTAL(9,cell:cell)

9 represents the function being used (SUM), followed by the range of cells the function is operating on.

The neat thing about using the Subtotal function is that if you have used it several times in the same column or row, clicking on the AutoSum button at the end of the column or row will make Excel add only the results of cells containing the Subtotal function in that column or row.

View all Excel hints and tips