VBA Worksheet Code

[ninjalearner]

Hi All,.


I am a learner in VBA and i need help with the following VBA code


I have an open workbook that contains 20 named worksheets.I have a list of 42 departments listed on range("A2:A43") in my sheet1. The 20 worksheets are named after the department list on sheet1.I am Looking for a VBA code that will Go through the cell contents of the range in sheet1 then loop through the names of the 20 worksheets in the workbook, then identify every name in sheet1 that deosnt have a worksheet opened in its name, then go ahead and open the worksheet and name it appropriately. This will continue untill it has opened all required worksheets named after the departments in range A2:A43.


This code will conclude by looping through all the worksheets and if there is any worksheet not named after a value in range A2.A43 will delete that worksheet from the workbook.

 

[Luke M]

Assumption is made that the name of the sheet with list of names is included in the list.

[pre]

Sub NameSheets()
Dim MyRange As Range, c As Range
Dim TestRange As Range
Dim ws As Worksheet
Dim x As Integer

Set MyRange = Worksheets("Sheet1").Range("A2:A43")
Application.ScreenUpdating = False

'Get rid of unused sheets
For Each ws In ThisWorkbook.Worksheets
x = 0
On Error Resume Next
x = WorksheetFunction.Match(ws.Name, MyRange, 0)
On Error GoTo 0
If x >= 1 Then
'Do nothing, worksheet name found
Else
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws

'Create sheets that are needed
For Each c In MyRange
Set TestRange = Nothing
On Error Resume Next
Set TestRange = Worksheets(c.Value).Range("A1")
On Error GoTo 0

If TestRange Is Nothing And c <> "" Then
Worksheets.Add after:=Worksheets(ThisWorkbook.Worksheets.Count)
ActiveSheet.Name = c.Value
End If
Next c

Application.ScreenUpdating = True
End Sub

[/pre]

 

[ninjalearner]

Hi Luke,

Thank you for the code. I have tried to run it. The delete aspect of it has a problem. It returns an error "method delete of object_worksheet failed" When i end debug and run it deletes the whole worksheets and leaves only one behind. Could the problem be because the worksheets are named as texts not numbers. When i look at the part of code that does the deleting, it looks like it searches for ranges greater than 1 and deletes the whole.


I will appreciate any help u can give to help resolve the deleting problem

 

[ninjalearner]

Hi Luke,

Thank you for the code. I have tried to run it. The delete aspect of it has a problem. It returns an error "method delete of object_worksheet failed" When i end debug and run it deletes the whole worksheets and leaves only one behind. Could the problem be because the worksheets are named as texts not numbers. When i look at the part of code that does the deleting, it looks like it searches for ranges greater than 1 and deletes the whole.


I will appreciate any help u can give to help resolve the deleting problem

 

[NARAYANK991]

Hi ,


I am sure Luke will resolve your problem ; in the meantime , if you want , you can try out this , which is Luke's code , modified a little.

[pre]

Sub NameSheets()
Dim ws As Worksheet
Dim Range_data As Variant
Dim i As Integer

Range_data = Worksheets("Sheet1").Range("A2:A43").Value
Application.ScreenUpdating = False

' Create sheets that are needed
For i = 1 To UBound(Range_data, 1)
Set ws = Nothing
On Error Resume Next
Set ws = ThisWorkbook.Worksheets(Range_data(i, 1))
On Error GoTo 0
If ws Is Nothing Then
ThisWorkbook.Worksheets.Add.Name = Range_data(i, 1)
End If
Next

' Get rid of unused sheets
For Each ws In ThisWorkbook.Worksheets
delete_ws = True
For i = 1 To UBound(Range_data, 1)
If ws.Name = Range_data(i, 1) Then
delete_ws = False
End If
Next
If delete_ws Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next
Application.ScreenUpdating = True
End Sub

[/pre]
Narayan

 

[ninjalearner]

Thank You Huys for the help. The code by Narayank Works. Please how do i exclude the original sheet 1 from being removed during the deleting.


Thanks

 

[NARAYANK991]

Hi ,


As Luke has already pointed out , just include the name Sheet1 in the list , if that is possible ; if this is not possible then the code has to be modified so that Sheet1 is not deleted. Can you do this modification yourself ?


You need to modify the following IF statement in the already posted code :


If ws.Name = Range_data(i, 1) Then


to the following :


If ((ws.Name = Range_data(i, 1)) Or ( ws.Name = "Sheet1" )) Then


Try this modification , and let us know if it works.


Narayan