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 · Accepted Answer · 2018-12-18 14:02:17Z

2

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.

answered Dec 18, 2018 at 14:02

Cyril

6,8291 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Stäcker Over a year ago

Thanks @Cyril for the detailled.answer. The problem is, that I do not want to delete the sheet in the source workbook, but in the destination workbook where a last month version of the sheet is stored. So I want to transfer multiple sheets from WB A to WB B. WB B already has the sheets from last month and some other important sheets that can not be deletet. What I want to do is to overwrite Sheets A B and C in WB B with the new Sheets A B and C from WB A. Since there is no overwrite possibility (as far as I know), I have to delete Sheets A B and C in WB B first, then copy from WB A.

Tim Stäcker Over a year ago

My plan was to reference the sheet names by their name to delete the correct sheets in WB B and not by their sheet number. Is there any way to make this work by referencing their real worksheet name?

Tim Stäcker Over a year ago

Okay I got it. Your answer helped me! Thanks a lot @Cyril. Didn't really read your first sentence. Have a nice christmas!

Collectives™ on Stack Overflow

Excel VBA Reference variable sheet name

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related