workbook

[Ranjith Kurian]

Hi,

i hv a excel workbook called as Raw.xls, i need a macro code to check in
Raw.xls whether the sheet name 'Cat' is there, if not there then the macro
need to add a new sheet called as 'Cat'

 

[Jacob Skaria]

Hi Ranjith

Try the below macro...Edit the file path to suit

Sub AddSheet()
Dim wb As Workbook, ws As Worksheet
Set wb = Workbooks.Open("c:\raw.xls")
On Error Resume Next
Set ws = wb.Sheets("Cat")
If ws Is Nothing Then
Set ws = wb.Worksheets.Add(after:=wb.Sheets(wb.Sheets.Count))
ws.Name = "Cat"
End If
wb.Close True
End Sub

If this post helps click Yes

 

[Jarek Kujawa]

Sub cus()
For Each Sheet In Workbooks("Raw.xls").Sheets
If Sheet.Name = "Cat" Then cnt = cnt + 1
Next

If cnt = 0 Then Sheets.Add
ActiveSheet.Name = "Cat"

End Sub

 

[Sam Wilson]

Sub demo()

Dim wb As Workbook
Dim ws As Worksheet

Dim bFlag As Boolean
bFlag = False

'You only need one of the two following lines - 1st if it's already open,
2nd if it isn't
Set wb = Workbooks("Raw.xls")
Set wb = Workbooks.Open("C:\path\Raw.xls")

For Each ws In wb.Worksheets
If ws.Name = "Cat" Then bFlag = True
Next ws

If bFlag = False Then
Set ws = wb.Worksheets.Add
ws.Name = "Cat"
End If

End Sub

 

[Ranjith Kurian]

thanks a lot

Hi Ranjith

Try the below macro...Edit the file path to suit

Sub AddSheet()
Dim wb As Workbook, ws As Worksheet
Set wb = Workbooks.Open("c:\raw.xls")
On Error Resume Next
Set ws = wb.Sheets("Cat")
If ws Is Nothing Then
Set ws = wb.Worksheets.Add(after:=wb.Sheets(wb.Sheets.Count))
ws.Name = "Cat"
End If
wb.Close True
End Sub

If this post helps click Yes