whatsapro
05-20-2016, 01:54 PM
I'm trying to create sheets from cell values only if they are not blank or contain "#N/A".
For example, I have Q3:Q38 that contain values I want to turn into sheets automatically. However, some of "#N/A" and others are blank. I do not wan tot create sheets for those ones.
Column Q
31114A
36516A
#N/A
22492A
22334A
#N/A
So the end result would be 4 sheets created, "31114A", "36516A", "22492A" and "22334A".
Here is some code I found online that does this except it creates the blanks and #N/A sheets as well.
Sub AddSheets()
Dim cell As Excel.Range
Dim wsWithSheetNames As Excel.Worksheet
Dim wbToAddSheetsTo As Excel.Workbook
Set wsWithSheetNames = ActiveSheet
Set wbToAddSheetsTo = ActiveWorkbook
For Each cell In wsWithSheetNames.Range("Q3:Q38")
With wbToAddSheetsTo
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = cell.Value
If Err.Number = 1004 Then
Debug.Print cell.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next cell
End Sub
Any help would be appreciated.
Thanks.
For example, I have Q3:Q38 that contain values I want to turn into sheets automatically. However, some of "#N/A" and others are blank. I do not wan tot create sheets for those ones.
Column Q
31114A
36516A
#N/A
22492A
22334A
#N/A
So the end result would be 4 sheets created, "31114A", "36516A", "22492A" and "22334A".
Here is some code I found online that does this except it creates the blanks and #N/A sheets as well.
Sub AddSheets()
Dim cell As Excel.Range
Dim wsWithSheetNames As Excel.Worksheet
Dim wbToAddSheetsTo As Excel.Workbook
Set wsWithSheetNames = ActiveSheet
Set wbToAddSheetsTo = ActiveWorkbook
For Each cell In wsWithSheetNames.Range("Q3:Q38")
With wbToAddSheetsTo
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = cell.Value
If Err.Number = 1004 Then
Debug.Print cell.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next cell
End Sub
Any help would be appreciated.
Thanks.