Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another 'Sheet Exists' Question...
Dear All
I'm struggling with working out if a sheet exists in my workbook. I've used code posted from this group but need to tweek it to create a sheet name based on a couple of cell values. When I use the code below, Run Time Error 9 is returned. myShtName creates a string 'prd1 Product Name' - this is the sheet name I'm looking for - if it exists then no action is needed, otherwise copy a template sheet at the end of the workbook, and name the sheet 'prd1 Product Name'. As usual, any help gratefully appreciated Trevor. -------------------------------------- Dim rng As Range, cell As Range Dim ws As Workksheet Dim myShtName As String Set rng = Range("productlist") For Each cell In rng If cell < "" Then myShtName = "prd" & cell.Offset(0, -1).Value & " " & cell.Value Set ws = Worksheets(myShtName) If ws Is Nothing Then Sheets("Product Template").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = myShtName Range("B3") = cell.Value End If End If Next cell |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another 'Sheet Exists' Question...
Hi Trevor,
The Run Time Error 9 is returned when you try and set a reference to a sheet that doesn't exist. That's fine but do it like this - On Error Resume Next Set ws = Nothing Set ws = Worksheets(myShtName) On Error goto 0 ' or resume normal error handler If ws is nothing then 'etc Are you really wanting to potentially insert a sheet in each loop. If so, or if 'ws' may already refer to a sheet, note the line set ws = Nothing before testing. Regards, Peter T "Trevor Williams" wrote in message ... Dear All I'm struggling with working out if a sheet exists in my workbook. I've used code posted from this group but need to tweek it to create a sheet name based on a couple of cell values. When I use the code below, Run Time Error 9 is returned. myShtName creates a string 'prd1 Product Name' - this is the sheet name I'm looking for - if it exists then no action is needed, otherwise copy a template sheet at the end of the workbook, and name the sheet 'prd1 Product Name'. As usual, any help gratefully appreciated Trevor. -------------------------------------- Dim rng As Range, cell As Range Dim ws As Workksheet Dim myShtName As String Set rng = Range("productlist") For Each cell In rng If cell < "" Then myShtName = "prd" & cell.Offset(0, -1).Value & " " & cell.Value Set ws = Worksheets(myShtName) If ws Is Nothing Then Sheets("Product Template").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = myShtName Range("B3") = cell.Value End If End If Next cell |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another 'Sheet Exists' Question...
Hi
.... Dim ws As Object Dim varSourceSheet As String Dim varCont As Boolean .... varSourceSheet = "prd1 Product Name" For Each ws In Worksheets varCont = IIf(varCont, True, ws.Name = varSourceSheet) Next ws If varCont Then ' your actions Else ' your actions End If .... -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Trevor Williams" wrote in message ... Dear All I'm struggling with working out if a sheet exists in my workbook. I've used code posted from this group but need to tweek it to create a sheet name based on a couple of cell values. When I use the code below, Run Time Error 9 is returned. myShtName creates a string 'prd1 Product Name' - this is the sheet name I'm looking for - if it exists then no action is needed, otherwise copy a template sheet at the end of the workbook, and name the sheet 'prd1 Product Name'. As usual, any help gratefully appreciated Trevor. -------------------------------------- Dim rng As Range, cell As Range Dim ws As Workksheet Dim myShtName As String Set rng = Range("productlist") For Each cell In rng If cell < "" Then myShtName = "prd" & cell.Offset(0, -1).Value & " " & cell.Value Set ws = Worksheets(myShtName) If ws Is Nothing Then Sheets("Product Template").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = myShtName Range("B3") = cell.Value End If End If Next cell |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another 'Sheet Exists' Question...
Brilliant, thanks Peter - works a treat!
"Peter T" wrote: Hi Trevor, The Run Time Error 9 is returned when you try and set a reference to a sheet that doesn't exist. That's fine but do it like this - On Error Resume Next Set ws = Nothing Set ws = Worksheets(myShtName) On Error goto 0 ' or resume normal error handler If ws is nothing then 'etc Are you really wanting to potentially insert a sheet in each loop. If so, or if 'ws' may already refer to a sheet, note the line set ws = Nothing before testing. Regards, Peter T "Trevor Williams" wrote in message ... Dear All I'm struggling with working out if a sheet exists in my workbook. I've used code posted from this group but need to tweek it to create a sheet name based on a couple of cell values. When I use the code below, Run Time Error 9 is returned. myShtName creates a string 'prd1 Product Name' - this is the sheet name I'm looking for - if it exists then no action is needed, otherwise copy a template sheet at the end of the workbook, and name the sheet 'prd1 Product Name'. As usual, any help gratefully appreciated Trevor. -------------------------------------- Dim rng As Range, cell As Range Dim ws As Workksheet Dim myShtName As String Set rng = Range("productlist") For Each cell In rng If cell < "" Then myShtName = "prd" & cell.Offset(0, -1).Value & " " & cell.Value Set ws = Worksheets(myShtName) If ws Is Nothing Then Sheets("Product Template").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = myShtName Range("B3") = cell.Value End If End If Next cell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to check to see if a sheet with a particular name exists? | Excel Worksheet Functions | |||
If sheet exists, then... | Excel Programming | |||
Question: File already exists | Excel Programming | |||
Sheet name already exists | Excel Discussion (Misc queries) | |||
How can I know if a sheet exists ? | Excel Programming |