Excel Filename – Copy sheets to new file

In the previous post I posted a code to copy data and remove all external references and save as a new file. But for a simple case, I was trying to save a sheet – as it is – as a new file.

But, typical to a copy-paste code; a strange error occurred while I tried to get the filename of current sheet through an add-in. I wanted the filename of the excel file I was editing but I kept on receiving the name of add-in. It took me a while to realize that ‘ActiveWorkbook’ and ‘ThisWorkbook’ behave differently.

?ActiveWorkbook.FullName
C:\Documents and Settings\XXXX\Application Data\Microsoft\AddIns\test.xla

?ThisWorkbook.FullName
C:\Test\test.xlsx

I was copying a sheet and saving it as a new file. If you want to do the same, following subroutine can be handy :

Sub CopyMySheet()
Dim DstFile As String 'Destination File Name
Dim CurrentFileName As String
Dim wb As Workbook
CurrentFileName = ActiveWorkbook.FullName ' returns C:\Test\test.xlsx
CurrentFileName = Replace(CurrentFileName, ".xlsx", "")
DstFile = CurrentFileName & "_new" & ".xlsx" ' returns C:\Test\test_new.xlsx
Worksheets("st_orig").Copy 'Copying original sheet
Set wb = ActiveWorkbook
wb.SaveAs DstFile 'Saving the copy
wb.Close 'Close the file
End Sub

Incoming search terms:

• copy a xlsx batch file
• vba to save as xlsx with current name in Excel
• vba mac close workbook
• vba copying file name to sheet
• sheet filename
• paste copied data to new file using vba
• ms excel vba Set wb Dst = ActiveWorkbook
• excel vba save file as unicode
• excel vba 2010 paste to active workbook
• error on wb SaveAs DstFile

Excel VBA – Copy worksheets and save as a new file

If you copy data from one sheet to another the formula refer back to the original sheet. Doing a ‘past special’ and copying only the data can do the trick. But if you need to do such operations for a large number of sheets a VBA macro can be handy:

Sub CopyPasteSave()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet

ErrCatcher:
MsgBox "Specified sheets do not exist within this workbook"
End Sub

This code will ask for the new file name before it closes. You can tweak the code to suit your requirement.

Incoming search terms:

• excel vba save worksheet as new file
• vba copy sheet to new file
• excel 2010 vba save worksheet
• vba ws copy options
• vba excel 2010 copy sheet
• vba copy worksheet values to new workbook
• vba copy worksheet to new workbook
• vba copy multiple files new worksheet
• vba copy array sheets
• vba copy array of worksheets

Batch printing in MS Excel

I had to print loads of charts from excel files. All at once! Rather than starting printing one by one I started looking for software to do the job for me. I found some really nice featured software but they came with hefty price tags (not less than 100 bucks). At last I found just what I want. A free, easy batch printer for Excel, batprint.xla!

But the only problem with it is all files need to be opened before printing. But, that is ok with me as I can select each sheets I want to print (the feature some paid software do not have).

If you want the tool you can download it from

http://opac35.free.fr/dynamicmap/prndiff_xla_us.htm

The page has detailed instruction on how to install and use it.
I have a copy of it. If the link cease to work I can always upload for you.
Enjoy.

Incoming search terms:

• excel batch print
• batch print excel
• (excel OR word OR ppt) batch print
• batch printing in excel
• batch printing in excel 2010
• batch printing tool for excel
• bulk printing in excel

Free Excel add-in The Duplicate Master

Update: Duplicate master V2.16  (Download link). (04/08/10)

David Brett (a.k.a. brettjd) wrote an excellent utility for removing duplicates within Excel. But as his original web site is gone offline, people are deprived from using the great utility.

The add-in works in MS Excel 2003 and 2007. It was originally distributed through the site – http://members.iinet.net.au/~brettdj which is offline these days.

You can however download the the duplicate master xls file and use it for free.

Edit: Thanks to ElmasnoJ the duplicate master has got a companion – the help file. You can now download the help file for using the excel add-in.

Edit (Feb 2, 2010): Dave has released the new version of the add-in, Duplicate Master Version 2.13, Please go to the comment for the download link.

Incoming search terms:

• duplicate master
• The Duplicate Master
• duplicate master excel
• excel duplicate master
• excel duplicate master download
• duplicate master for excel
• excel 2010 remove duplicate add in
• excel add on duplicates
• excel duplicate manager add in for mac
• duplicatemaster