The UK's Number 1 for Microsoft Office Training

Sitemap

Bookmark page

Printable version

Course updates
due to adverse
weather conditions

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Anthony as discussed??

	98.3 % recommend us
	More live stats...

Bookmark this page

Share this with a friend or colleague:

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Anthony as discussed??

Resolved · Medium Priority · Version 2003

ReplyFri 22 Jan 2010, 16:19Delegate Rudolph said...

Anthony as discussed??

How can the results of a query be created in a new file into a completely different folder, after running the macro for reports?

For upcoming training course dates see: Pricing & availability

ReplySat 23 Jan 2010, 17:26Trainer Anthony said...

RE: Anthony as discussed??

Hi Rudolph, 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.

Excel articles

· So Why Are There So Many File Types In Excel 2007?
· Excel and Inventory Management
· How To Add A Second Axis To An Excel Line Chart
· Summing Up Scenarios With Excel
· How To Create An Excel Formula Using Data From Several Worksheets

Please browse our web site to find out more about
advanced excel seminar training and other Microsoft training courses.

Excel tip:

Sum Up All the Values in A Column

If you want to quickly calculate the Summed values of all cells in a column in Excel 2003 normally you would use the SUM formula. (eg if you wanted to calculate the values in Column C rows 10 to 25) the formula would be:

=SUM(C10:C25)

However, if you keep adding values to column C you would keep having to modify the above SUM formula which can get quite annoying.

To get around this you can sum all the values in a column using the following formula:

=SUM(COLUMN:COLUMN)

Which, in our example, would be:

=SUM(C:C)

NOTE You cannot place this formula in column C, or else Excel 2003 will show a circular reference error.

The formula must be placed in any other column, EXCEPT the one being calculated.

View all Excel hints and tips

Hi,

Is there a quick way of removing all constraints on a plan I have without removing each one individually?

Many Thanks

Debi

» Forum post: Constraints

Rate this page:

Currently 2.15/5
1
2
3
4
5

2.2/5 (213 votes cast)

Add to:

Follow @beststl on Twitter

© 2012 Best STL. All Rights Reserved. A Microsoft software training company. info
All prices offered for business users and exclude VAT. E&OE · Terms and Conditions