How to execute a VBA command on a sheet with a specific name?

Question

How to execute a VBA command on a sheet with a specific name?

Navigation

#1 by (0 votes)

1

I have an Excel document with multiple sheets. In this document I need to execute a command on a specific sheet, ie the sheet name. But if this sheet does not exist run another command on another specific named sheet until the sheets are finished.

Example:

Sub adicionar_categorias()

folhas = Array ("FOLHA 1", "FOLHA 2", "FOLHA 3")

For i = LBound(folhas) To UBound(folhas)
Worksheets(folhas(i)).Activate

Range("X2").Select
ActiveCell.FormulaR1C1 = "Computadores>Portáteis"
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("X2").AutoFill Destination:=Range("X2:X" & Lastrow)

Next i
End Sub

If the sheet is not in the book it will give Worksheets (sheets (i)) error. ! How do I resolve the error?

RESOLVED: On Error Resume Next

Sub adicionar_categorias()

folhas = Array ("FOLHA 1", "FOLHA 2", "FOLHA 3")
On Error Resume Next
For i = LBound(folhas) To UBound(folhas)
Worksheets(folhas(i)).Activate

Range("X2").Select
ActiveCell.FormulaR1C1 = "Computadores>Portáteis"
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("X2").AutoFill Destination:=Range("X2:X" & Lastrow)

Next i
End Sub

Thank you "dot.Py!

vba excel-vba

asked by anonymous 25.05.2017 / 22:41

1 answer

CoffeeScript on Rails - select items from a table function savefile (); exports default to downloads folder

score 0 · Accepted Answer

According to documentation , you can call the .Activate method, which is equivalent to clicking the worksheet tab named as "Plan1" in your workbook.

Worksheets("Plan1").Activate

Instead of referencing by name, you can reference the position of the tab in your workbook, for example:

Worksheets(0).Activate
Worksheets(1).Activate
Worksheets(2).Activate

Where in a newly created workbook, Worksheets(0) is equal to Plan1 , Worksheets(1) to Plan2 , and Worksheets(2) to Plan3 .

Remembering that if you change the order of the tabs manually, even though the name does not change, the position will change ...

You can also create a array with the names of the worksheets you want to enable, and run a for each item within that array.

planilhas = ("plan1", "plan2", "plan3", "plan4")

For i = 1 to UBound(planilhas)
    Worksheets(planilhas(i)).Activate
Next i

To improve this code, you can attach a try/catch/finally or a On Error Resume Next so that the program knows what to do when the tab exists, and what to do when it does not.