Programatically copy worksheet via VBA in Excel

Dim oBook As Workbook

Excel.Application.DisplayAlerts = False

Application.ScreenUpdating = False

Me.btnExportWorkSheet.TakeFocus<nClick = False

' Delete the Old File

Kill "c:\FileName.xls"

' Create a new blank workbook:

Set oBook = Application.Workbooks.Add

Application.SheetsInNewWorkbook = 1

' Add a defined name to the workbook

' that RefersTo a range:

oBook.Names.Add Name:="tempRange", RefersTo:="=Sheet1!$A$1"


' Save the workbook:

oBook.SaveAs "c:\FileName.xls"


' Select the Workbook where the Worksheet to be copied is located

Workbooks("Old File.xls").Activate

Workbooks("Old File.xls").Worksheets("Work Sheet A").Activate


' Copy the Worksheet to the Workbook

Worksheets("Work Sheet A").Copy Before:=Workbooks("New Work

Book.xls").Sheets(1)

Dim xRange As Range, adr As String

Workbooks("New Work Book.xls").Worksheets("Work Sheet A").Activate

Workbooks("New Work Book.xls").Worksheets("Work Sheet A").Range("a1:z100").Activate

' Remove Links and Replace with Cell Values

With Workbooks("New Work Book.xls").Worksheets("Work Sheet A")

Dim cCell As Range

Dim strValue As String

Set xRange = .Range("a1:z100")

For Each cCell In xRange

' Save the value to use as a means to identify what cells

to change

strValue = CStr(cCell.Value)

If InStr(strValue, "/") > 0 Then

cCell = CStr(cCell.Value)

End If

Next

End With

' Select the next Workbook where the Worksheet to be copied is located

Workbooks("Old File.xls").Activate

Workbooks("Old File.xls").Worksheets("Work Sheet B").Activate

' Copy the Worksheet to the Workbook

Worksheets("Work Sheet B").Copy Before:=Workbooks("CashFlowInput&DollarChart.xls").Sheets(1) ', UpdateLinks:=0

Workbooks("New Work Book.xls").Worksheets("Work Sheet B").Activate

Workbooks("New Work Book.xls").Worksheets("Work Sheet B").Range("a1:z100").Activate

' Remove Links and Replace with Cell Values

With Workbooks("New Work Book.xls").Worksheets("Dollars Chart")

Set xRange = .Range("a1:z100")

For Each cCell In xRange

' Save the value to use as a means to identify what cells to change

' cCell.Formula will show the link back to the Original

Workbook

strValue = CStr(cCell.Formula)

If InStr(strValue, "!") > 0 Then

cCell = CStr(cCell.Value)

' If cCell = .Range("b51") Or cCell = .Range("m4") Then

' Stop

' End If

End If

Next

End With

MsgBox ("Your Worksheets have been copied to C:\New Work Book.xls")

End Sub

Sort Excel workbook sheet alphabatically

Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
If iAnswer = vbYes Then
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
'
' If the answer is No, then sort in descending order.
'
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
End Sub

MIME - File Context Types

Ai  application/postscript
aif  audio/x-aiff
aifc  audio/x-aiff
aiff audio/x-aiff
asc  text/plain
atom  application/atom+xml
au   audio/basic
avi   video/x-msvideo
bcpio   application/x-bcpio
bin   application/octet-stream
bmp   image/bmp
cdf   application/x-netcdf
cgm   image/cgm
class   application/octet-stream
cpio   application/x-cpio
cpt   application/mac-compactpro
csh   application/x-csh
css   text/css
dcr   application/x-director
dif   video/x-dv
dir   application/x-director
djv   image/vnd.djvu
djvu   image/vnd.djvu
dll   application/octet-stream
dmg   application/octet-stream
dms   application/octet-stream
doc   application/msword
dtd   application/xml-dtd
dv   video/x-dv
dvi   application/x-dvi
dxr   application/x-director
eps   application/postscript
etx   text/x-setext
exe   application/octet-stream
ez   application/andrew-inset
gif   image/gif
gram   application/srgs
grxml   application/srgs+xml
gtar   application/x-gtar
hdf   application/x-hdf
hqx   application/mac-binhex40
htm   text/html
html   text/html
ice   x-conference/x-cooltalk
ico   image/x-icon
ics   text/calendar
ief   image/ief
ifb   text/calendar
iges   model/iges
igs   model/iges
jnlp   application/x-java-jnlp-file
jp2   image/jp2
jpe   image/jpeg
jpeg   image/jpeg
jpg   image/jpeg
js   application/x-javascript
kar   audio/midi
latex   application/x-latex
lha   application/octet-stream
lzh   application/octet-stream
m3u   audio/x-mpegurl
m4a   audio/mp4a-latm
m4b   audio/mp4a-latm
m4p   audio/mp4a-latm
m4u   video/vnd.mpegurl
m4v   video/x-m4v
mac   image/x-macpaint
man   application/x-troff-man
mathml  application/mathml+xml
me   application/x-troff-me
mesh   model/mesh
mid   audio/midi
midi   audio/midi
mif   application/vnd.mif
mov   video/quicktime
movie   video/x-sgi-movie
mp2   audio/mpeg
mp3   audio/mpeg
mp4   video/mp4
mpe   video/mpeg
mpeg   video/mpeg
mpg   video/mpeg
mpga   audio/mpeg
ms   application/x-troff-ms
msh   model/mesh
mxu   video/vnd.mpegurl
nc   application/x-netcdf
oda   application/oda
ogg   application/ogg
pbm   image/x-portable-bitmap
pct   image/pict
pdb   chemical/x-pdb
pdf   application/pdf
pgm   image/x-portable-graymap
pgn   application/x-chess-pgn
pic   image/pict
pict   image/pict
png   image/png
pnm   image/x-portable-anymap
pnt   image/x-macpaint
pntg   image/x-macpaint
ppm   image/x-portable-pixmap
ppt   application/vnd.ms-powerpoint
ps   application/postscript
qt   video/quicktime
qti   image/x-quicktime
qtif   image/x-quicktime
ra   audio/x-pn-realaudio
ram   audio/x-pn-realaudio
ras   image/x-cmu-raster
rdf   application/rdf+xml
rgb   image/x-rgb
rm   application/vnd.rn-realmedia
roff   application/x-troff
rtf   text/rtf
rtx   text/richtext
sgm   text/sgml
sgml   text/sgml
sh   application/x-sh
shar   application/x-shar
silo   model/mesh
sit   application/x-stuffit
skd   application/x-koan
skm   application/x-koan
skp   application/x-koan
skt   application/x-koan
smi   application/smil
smil   application/smil
snd   audio/basic
so   application/octet-stream
spl   application/x-futuresplash
src   application/x-wais-source
sv4cpio application/x-sv4cpio
sv4crc  application/x-sv4crc
svg   image/svg+xml
swf   application/x-shockwave-flash
t    application/x-troff
tar   application/x-tar
tcl   application/x-tcl
tex   application/x-tex
texi   application/x-texinfo
texinfo application/x-texinfo
tif   image/tiff
tiff   image/tiff
tr   application/x-troff
tsv   text/tab-separated-values
txt   text/plain
ustar   application/x-ustar
vcd   application/x-cdlink
vrml   model/vrml
vxml   application/voicexml+xml
wav   audio/x-wav
wbmp   image/vnd.wap.wbmp
wbmxl   application/vnd.wap.wbxml
wml   text/vnd.wap.wml
wmlc   application/vnd.wap.wmlc
wmls   text/vnd.wap.wmlscript
wmlsc   application/vnd.wap.wmlscriptc
wrl   model/vrml
xbm   image/x-xbitmap
xht   application/xhtml+xml
xhtml   application/xhtml+xml
xls   application/vnd.ms-excel
xml   application/xml
xpm   image/x-xpixmap
xsl   application/xml
xslt   application/xslt+xml
xul   application/vnd.mozilla.xul+xml
xwd   image/x-xwindowdump
xyz   chemical/x-xyz
zip   application/zip