How do I: Reference a sheets 'codename' and increment? - VBA

[NRGZ]

How do i increment a sheets 'codename' so that it can be referenced?
Sheet 'codename' - (quoted from - Using sheet codenames in Microsoft Excel VBA - )'
if a user subsequently modified the tab name again - to "JanSales", for instance - then the existing code would generate a run-time error as VBA would not be able to find the sheet.

In order to avoid this common problem, you can use the sheet's codename (the codename is the part that remained as Sheet1 in the two examples above) in your VBA code, as shown below:

Sheet1.Select'
--
As part of what i'm trying to achieve is, I import a number of CSV files that when imported rename the 'TAB Names' so the order and name will always change, this is why i need to use the sheets 'codename'.
I need to run a number of different SUBs to do different jobs with the data but to begin with i need to retrieve some information and put it into a sheet called 'TOTALS',
My previous code worked fine but fell over when the tab names went dynamic,

Sheets("Totals").Cells(a, 1).Value = Sheet & a.Cells(a, 1).Value
' Sheets("Totals").Cells(a, 1).Value = Sheets("sheet" & a).Cells(a, 1).Value
Sheets("Totals").Cells(a, 2).Value = Sheets("sheet" & a).Cells(a, 2).Value
Sheets("totals").Cells(a, 4).Value = WorksheetFunction.Sum(Worksheets("sheet" & a).Range(Worksheets("sheet" & a).Cells(2, 3), Worksheets("sheet" & a).Cells(999, 3)))
Sheets("totals").Cells(a, 3).Value = WorksheetFunction.CountA(Worksheets("sheet" & a).Range(Worksheets("sheet" & a).Cells(2, 4), Worksheets("sheet" & a).Cells(999, 4)))

a = a + 1

Loop

so i assumed that 'sheet & a' would work but it doesnt and as i use the 'sheet' reference in many other places can sheet 'codename' still work in those other places?

Thanks,

 

[RoryA]

You can't do that with a codename. You could use a separate function that gets a worksheet object based on its codename but I suspect there would be simpler ways if you are controlling the import in the first place.

 

[NRGZ]

...bugger!
I'm using this code to import the CSVs,

Sub ImportCSVs()

'Author:    Jerry Beaucaire
'Date:      8/16/2010
'Summary:   Import all CSV files from a folder into separate sheets
'           named for the CSV filenames

'Update:    2/8/2013   Macro replaces existing sheets if they already exist in master workbook
'Update: base script as seen in: https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/merge-functions/csvs-to-sheets
'Update: adjusted code to work in Excel 2016

Dim fPath   As String
Dim fCSV    As String
Dim wbName  As String
Dim wbCSV   As Workbook
Dim wbMST   As Workbook


wbName = "this is a string"
Set wbMST = ThisWorkbook

fPath = "C:\csvs\"                  'path to CSV files, include the final \
Application.ScreenUpdating = False  'speed up macro
Application.DisplayAlerts = False   'no error messages, take default answers
fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

    On Error Resume Next
    Do While Len(fCSV) > 0
        Set wbCSV = Workbooks.Open(fPath & fCSV)                    'open a CSV file
        If wbName = "this is a string" Then 'this is to check if we are just starting out and target workbook only has default Sheet 1
            wbCSV.Sheets.Copy After:=wbMST.Sheets(2) 'for first pass, can leave as is. if loading a large number of csv files and excel crashes midway, update this to the last csv that was loaded to the target workbook
        Else
            wbCSV.Sheets.Copy After:=wbMST.Sheets(wbName) 'if not first pass, then insert csv after last one
        End If
        wbCSV.Close False            'closes each CSV after import
        fCSV = Dir                  'ready next CSV
        wbName = ActiveSheet.Name 'save name of csv loaded in this pass, to be used in the next pass
    Loop

Application.ScreenUpdating = True
Set wbCSV = Nothing
End Sub

So if,
wbName = ActiveSheet.Name 'save name of csv loaded in this pass, to be used in the next pass
is controlling the sheets names i should be able to add,
Activesheet.name = "sheet" & z
('z' would be an incremental number)

Yes...possible?

 

[RoryA]

Yes, there's no reason you can't name the sheets however you want at that point.

 

[NRGZ]

....adventures in VBA land
Researched how to change sheet names and doing it within the 'import' was trouble as it was trying to rename the sheets as it was importing but did some more trawling and decided to put it in its own SUB.

Sub sheet_namez()

    z = ThisWorkbook.Sheets.Count
   
    For y = 3 To z
    
    Sheets(y).Name = "sheet" & y

    Next y

End Sub

Thanks again for all the help.
o/