Saturday, December 1, 2007

VBA Worksheet

Delete worksheets
For Each ws In oWB.Worksheets
ws.Delete
Next


Add a worksheet
Sub Add_Sheet()
Dim wSht As Worksheet
Dim shtName As String

shtName = Format(Now, "mmmm_yyyy")

For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sheet already exists! "
Exit Sub

Else
Sheets.Add.Name = shtName
Sheets(shtName).Move After:=Sheets(Sheets.Count)
Sheets("Sheet1").Range("A1:A5").Copy _
Sheets(shtName).Range("A1")
End If

Next
End Sub


Copy a worksheet
Sub Copy_Sheet()
Dim wSht As Worksheet
Dim shtName As String

shtName = "NewSheet"

For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sheet already exists! "
Exit Sub

Else
Sheets(1).Copy before:=Sheets(1)
Sheets(1).Name = shtName
Sheets(shtName).Move After:=Sheets(Sheets.Count)
End If

Next
End Sub

0 comments:

 

Get paid for your opinions! Click on the banner above to join Planet Pulse. Its totally free to sign up, and you can earn UNLIMITED. Find out more by visiting PLANET PULSE.
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template