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
Advertisements

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

 

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?

What is the syntax to use/set data in another sheet?

'to retrieve data
Worksheets("<sheet name>").Range("<your range>").Value
Worksheets("<sheet name>").Cells(<row number>, <column number>).Value

'to set data
Worksheets("<sheet name>").Range("<your range>") = <your value/data>
Worksheets("<sheet name>").Cells(<row number>, <column number>).Value = <your value/data>
'examples
Worksheets("Example").Range("A1:A4") = "hello"
Worksheets("Example").Range("D1") = "world"
Worksheets("Example").Cells(2, 4).Value = "goodbye"

Capture.PNG