Ignor blank cells

**stevekirk** · 09-02-2006, 01:23 AM

hi,

i am trying to add worksheets to my work book by using information from certain cells. if by any chance one of the cells is left blank an error message appears ie d1

could you please look at the workbook and advise what is wrong please

thank you

steve

**VBA Noob** · 09-02-2006, 02:27 AM

Hi Try

On error resume next.

Look at the attached to learn more about error trapping

http://www.cpearson.com/excel/ErrorHandling.htm

VBA Noob

**stevekirk** · 09-03-2006, 04:17 AM

hi,

when i put the error trap in, i do not get the error message but a worksheet still is generated. could you please advise how to stop that

thanks for your help

steve

**davesexcel** · 09-03-2006, 03:44 PM

Hi Steve the last code I gave you didn't work so hot, it should have gone into a workbook module, here's the revised code

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Static lRows As Long
Dim lTemp As Long
'Only track the first sheet or by name
If Sh.Name = "Sheet1" Then
'Only if the changed cell is in Column A
If Target.Column = 1 Then
'Get the last row in the used range
lTemp = Sheet1.UsedRange.End(xlUp).Row
If lTemp > lRows Then
'Add a new sheet and rename it
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Target
End If
End If
End If
End Sub

Good luck