Exporting Selected Data To An Existing Excel File With VBA

An Excel spreadsheet often contains lots of data spread across different sheets in the same file. Sometimes you'll need to save the information in one sheet and use it in another Excel file. Some examples include:

Email the data in one sheet to a colleague without sending the whole fileUsing the information in one sheet to update another Excel fileSaving the data into another file which is preformatted and ready for printing

We'll look at a simple example where the objective will be to copy data in one sheet and save it into a new file.

Saving Data From One Sheet And Copying It Into Another File

To save data into another Excel spreadsheet, you'll need to know the location of the second file. The activework.path command returns the path of the active file and you can then define the second file location relative to the main file. One advantage is that it won't matter if the code is run on another machine, the code will identify the location.

First, we'll define the location and name of the new file and then select and copy the data we want to export.

Dim newFileLoc

newFileLoc = ActiveWorkbook.Path & "copy.xls"

Sheets(1).Activate

Cells.Select

Selection.Copy

Now we'll open a new Excel file and paste the data into sheet(1) which is the default and then close the new file.

Workbooks.Add

ActiveSheet.Paste

Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:=newFileLoc

ActiveWindow.Close

This a simple routine which copies the selected cells into the new file and saves it but there are a couple of issues to know about.

If you try to overwrite an existing file with the same name, an automatic alert pops up and asks if you want to replace the file. If you click the no button, an error occurs. One way around this is to use the application.displayalerts=false to ignore the alert, overwriting the file.

You could also use the file system object to determine whether the file already exists, but that technique is beyond the scope of this article. It really comes down to knowing what you want to achieve, and if you know the existing file should be overwritten then there's no problem.

Summary

This short snippet of code adds a little sophistication to a simple business process - extracting information and saving it. If you think about how many mouse clicks are involved in performing this task manually, it might pay to think about using VBA to make your work more efficient.

Andy L Gibson is a former Web Site programmer rediscovering his interest in Excel applications for small business. His blog at http://solutions4business.wordpress.com/ holds the VBA code for all of his articles on Excel and is available for FREE download. He is developing an Excel application which will contain working examples of VBA methods and solutions.

View the original article here