What is the syntax to access the right-most sheet?

Worksheets(Worksheets.Count)
'example
Worksheets(Worksheets.Count).Range("A1:A4") = "hello"
Worksheets(Worksheets.Count).Range("D1") = "world"
Worksheets(Worksheets.Count).Cells(2, 4).Value = "goodbye"
Advertisements

What is the difference between Worksheets and Sheets?

  • Worksheets include all worksheets in a workbook, excluding sheets who are type charts.
  • Sheets include all sheets in a workbook, including sheets who are type charts
  • If you are not using chart sheets, using either one yields the same results

What is the syntax to loop through worksheets?

'Method 1 - looping using a For Each loop
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
    <your code here>
Next sht

'Method 2 - looping using a For loop
For i=1 To ThisWorkbook.Worksheets.Count
     <your code here, referencing Worksheets(i)>
Next
'example using both methods
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
     sht.Range("B2") = "Hello"
Next sht

For i=1 To ThisWorkbook.Worksheets.Count
     Worksheets(i).Range("B2") = "Hello"
Next

 

What is the syntax to declare a worksheet object?

Dim <object name> As Worksheet
Set <object name> = Worksheets("<sheet name>")
'example of simplification with the worksheet object

'Original code
Worksheets("exampleSheet").Range("A1") = 6
Worksheets("exampleSheet").Range("B2:B9").Font.Italic = True
Worksheets("exampleSheet").Range("B2:B9").Interior.Color = rgbRed

'Simplified code
Dim sht As Worksheet
Set sht = Worksheets("exampleSheet")

sht.Range("A1") = 6
sht.Range("B2:B9").Font.Italic = True
sht.Range("B2:B9").Interior.Color = rgbRed

 

 

Tips for debugging when working with worksheets

When working with worksheets, you may get Run-Time Error:9. That is, the following message box may pop up during execution:

Capture.PNG

Tips for debugging this error:

  • Is the name of the Worksheets spelled correctly?
  • Has the name of the sheet changed and not been updated in your code?
  • Has the worksheet been deleted?
  • Has the index you used to reference the sheet too large?
  • If working with multiple workbooks, did you reference the wrong workbook?