Excel VBA Reference variable sheet name

Question

I want to reference to multiple sheets in a workbook and then copy those to another workbook with vba. This is all progress of a bigger Macro I build. The problem I have is, that I cannot reference the sheets of the for loop with their name. May be you guys can help. This is an extract of my code, all names are referenced at the top of my file (like sh As worksheet, and wb as workbook) What my macro should do, is check sheets if they should be send to a recipient, and some of those sheets (like the code below) merged into one workbook and then send this workbook. But the problem I have is, how to delete/overwrite a specific sheet in the destination workbook. (Line 5)

For Each sh In ThisWorkbook.Worksheets
    If sh.Range("A1").Value Like "?*@?*.?*" And sh.Name <> "A" Or sh.Name <> "B" Or sh.Name <> "C" Then
         Workbooks.Open Filename:= _
         "Path" & "Name" & ".xlsx", UpdateLinks:=0
        Sheets(chr(34)&sh.name&chr(34)).Delete
        Set wb = ActiveWorkbook
        sh.Copy Before:=wb.Worksheets(1)
        Set wb = ActiveWorkbook
        sh.Copy Before:=wb.Worksheets(1)
        Set sc = wb.Worksheets(chr(34)&sh.name&chr(34))
        With sc.UsedRange
        .Value = .Value
        Rows("244:310").Select
        Selection.EntireRow.Hidden = True
        ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
        Cells("B1").Select
        ActiveWindow.ScrollRow = 1
        End With

        Filename = "Name"

        Set wb = ActiveWorkbook
        Set OutMail = OutApp.CreateItem(0)
        Set OMail = OutApp.CreateItem(0)

        With wb
            .SaveAs FilePath & Filename & FileExtStr, FileFormat:=FileFormatNum
            On Error Resume Next
            With OMail
            .Display
            End With
                signature = OMail.HTMLbody
            With OMail
                .to = sh.Range("A1").Value
                .CC = sh.Range("A3").Value
                .BCC = ""
                .Subject = Text             
                .Attachments.Add.wb.FullName                                       

            End With

            On Error GoTo 0

Cyril Cyril · Accepted Answer · 2018-12-18T14:02:17

Since sheets inherently have a numeric representation, you can go between the sheets of a single workbook with a loop, such as:

Dim i as long
For i = 1 to sheets.count
    'do something using Sheets(i)
Next i

Regarding deletion of a sheets, you can use the above inclusive of the .delete:

Dim i as long
For i = 1 to sheets.count
    If Sheets(i).Range("A1").Value Like "?*@?*.?*" And Sheets(i).Name <> "A" Or Sheets(i).Name <> "B" Or Sheets(i).Name <> "C" Then  Sheets(i).Delete  'note that this is in-line
Next i

Additionally, if you don't want to use a loop, or simply want to look at the current sheet, you could use the below for the deletion:

ActiveSheet.Delete

I would recommend recording the sheet name as a string, before using in your if statement, purely for speed/efficiency (probably not noticeable for small bits of code, but decent practice), similar to:

Dim shName as String
shName = ActiveSheet.Name
If Sheets(shName).Range("A1").Value Like "?*@?*.?*" And shName <> "A" Or shName <> "B" Or shName <> "C" Then

This should allow VBA to utilize the already stored string (allocated memory) rather than going back to the name every time the call is made for that .name to be found.

Excel VBA Reference variable sheet name

1 Answers