I have three worksheets in a workbook namely "Client", "Collections" & "Interest". I want to use data from these worksheets to create new worksheets and also populate the corresponding values. As a start i manage to create new worksheets but it is also creating worksheets with no tab name. Please help me fix this:
Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Dim ws As Worksheet
Dim ws1 As Worksheet
Set MyRange = Sheets("Interest").Range("A5:A100")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
If MyCell.Value <> "" Then 'checks if cell is not empty
If Not SheetExists(MyCell.Value) Then 'check if worksheet already exists
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
'Copy worksheet("Client") to new worksheets
Sheets("Client").Cells.Copy
'paste to new worksheet
ActiveSheet.Paste
'Fill cell A1 with client name
For Each ws In ActiveWorkbook.Worksheets
ws.Range("B6") = ws.Name
Next
End If
End If
Next MyCell
End Sub
Function SheetExists(shName As String) As Boolean
SheetExists = False
For Each sh In Sheets
If sh.Name = shName Then
SheetExists = True
Exit For
End If
Next sh
End Function
Bookmarks