I finally decided to take another approach, which does not complicate my task and have used the simple way of selecting the range in each worksheet and fill in the the worksheet name and search for the code inserted at the range using a Vlookup technique. So finally made to work the way I wanted.
HTML Code:
Sub CreateMultipleWorksheets()
Dim r As Range
Dim CCGroup As String
Dim rng As Range
Sheets("CCTable").Select
For Each r In Range("A2", Range("A" & Rows.Count).End(xlUp))
If Not IsEmpty(r.Value) Then
If IsSheetExists(r.Value) Then
Set ws = Sheets(CStr(r.Value))
CCGroup = r.Value
Sheets(CCGroup).Select
Range("$E4").Value = CCGroup
Range("$D3").Value = "=VLOOKUP(" & CCGroup & ",Table1[#All],2,FALSE)"
Cells.Select
Selection.Copy
Else
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
ws.Name = r.Value
ws.Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
CCGroup = r.Value
Range("$E4").Value = CCGroup
Range("$D3").Value = "=VLOOKUP(" & CCGroup & ",Table1[#All],2,FALSE)"
End If
End If
Next
End Sub
Private Function IsSheetExists(ByVal txt As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Sheets(txt)
IsSheetExists = (Err.Number = 0)
On Error GoTo 0
End Function
Bookmarks