stapuff
10-08-2004, 07:53 AM
I have 2 files:
Setup.xls and Shipping Sheet.xls
On Setup.xls I have a button - Button1. On Button1_Click I would like to open up Shipping Sheet.xls and verify if Shipping Sheet.xls has a sheet tab name = to the value of B21 from sheet1 of Setup.xls formatted as MMM/YY if not insert it, name it, and make it the last sheet in the Shipping Sheet workbook.
I can some what follow and understand the SheetExist Function however I am still in need of some help. If I run the code below - it will insert a new sheet in Shipping Sheet.xls if one doesn't exist (in this case Oct 04). If one does exist - a generic sheet will then be created (sheet4, sheet5, etc.). What do I need to do to prevent sheets being added if the sheet already exists and how do I tie it back to the cell B21 in Setup.xls I am not very good at writing code.
Sub Button1_Click()
On Error Resume Next
Workbooks.Open Filename:="F:\Customer Service\Shipping Sheet.xls"
ActiveWorkbook.sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(Date, "MMM YY")
End Sub
Private Function SheetExists(sname) As Boolean' Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then
SheetExists = True _ Else SheetExists = False
End Function
Thanks,
Kurt
Setup.xls and Shipping Sheet.xls
On Setup.xls I have a button - Button1. On Button1_Click I would like to open up Shipping Sheet.xls and verify if Shipping Sheet.xls has a sheet tab name = to the value of B21 from sheet1 of Setup.xls formatted as MMM/YY if not insert it, name it, and make it the last sheet in the Shipping Sheet workbook.
I can some what follow and understand the SheetExist Function however I am still in need of some help. If I run the code below - it will insert a new sheet in Shipping Sheet.xls if one doesn't exist (in this case Oct 04). If one does exist - a generic sheet will then be created (sheet4, sheet5, etc.). What do I need to do to prevent sheets being added if the sheet already exists and how do I tie it back to the cell B21 in Setup.xls I am not very good at writing code.
Sub Button1_Click()
On Error Resume Next
Workbooks.Open Filename:="F:\Customer Service\Shipping Sheet.xls"
ActiveWorkbook.sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(Date, "MMM YY")
End Sub
Private Function SheetExists(sname) As Boolean' Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then
SheetExists = True _ Else SheetExists = False
End Function
Thanks,
Kurt