this nice code, courtesy by rorya, performs the following task ...
(1) looking for the word 'Sales' in the current worksheet,
(2) if found, creates a table with rngFound.CurrentRegion
(3) assigns/applies names and stylesfor these ListObjects
I would like to have a second version of this code ...
... where the created ranges 'just' get named with the following naming convention: range1, range2, range3 and no 'ListObjects' are created.
I hope this is feasible.
Thank you very much in advance. Regards, Andreas
Dim rngFound As Range
Dim strAddy As String
Dim lngCounter As Long
Set rngFound = ActiveSheet.UsedRange.Find(what:="Sales", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not rngFound Is Nothing Then
lngCounter = 1
strAddy = rngFound.Address
CreateTable rngFound.CurrentRegion, "Table" & lngCounter
lngCounter = lngCounter + 1
Set rngFound = ActiveSheet.UsedRange.FindNext(rngFound)
Loop While rngFound.Address <> strAddy
Sub CreateTable(rng As Range, strName As String)
With rng.Worksheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
.Name = strName
'No go in 2003
.TableStyle = "TableStyleLight2"