WORKSHEETS INFO: reference, loop, add, etcetera

erik.van.geit · Mar 21, 2007

Hi, all,

This is my (and yours if you want) "for future reference" post about WORKSHEETS.
(summary of my libraries and several sources: helpfiles, forumthreads, experience, ...)
You'll probably find the answer to some of your questions about worksheets.

New interesting information or relevant comments are WELCOME

Please do NOT post your specific project-oriented questions here. Anyway I'll not reply to those questions.

REFERENCING WORKSHEETS
To reference sheets when coding in VBA, you can use 3 properties
1. "code"name (in VBA)
2. "index" (relative TAB position)
3. "user"name (TAB name)
Each of the three styles has its pros and contras.

1. The codename is the VBA-name that Excel is automatically assigning to a sheet when created.

Code:

Sub Rename()
'using codename sheet1 you can reference the worksheet
'regardless of the "user" name
sheet1.Activate
sheet1.Name = InputBox("Please type sheetname", "RENAME SHEET1", sheet1.Name)
sheet1.Name = InputBox("Please type sheetname", "RENAME SHEET1", sheet1.Name)
End Sub

NOTE: The only time you cannot use a sheet's CodeName is when you reference a sheet that is in a workbook different from the one in which the code resides.

2. The index (position) of the sheet in the workbook is calculated by counting sheet-tabs from the left, so the left tab is the first sheet, etcetera ...
this referencestyle can be useful in loops

Code:

Sub view_123()
Dim I As Integer

For I = 1 To 3
Sheets(I).Select
MsgBox "You are viewing Sheet " & Sheets(I).Name & _
" which is at position " & I & " (counting from the left)", 64, "SHEET INFO"
Next I

End Sub

adding or moving sheets can disturb this code

3. The "user"name is what you see on the sheet tab.
used for "hardcoding"

Code:

Sub test()
Sheets("ProjectList").Select
End Sub

an error will occur in the code above when sheetname has changed

Within the VBE window you can see two referencestyles next to each other. The name to the left is the code name of the sheet and the name to the right (between parentheses) is the "user"name. For example, renaming Sheet1 to "ProjectList", the name in the project explorer would then appear as: Sheet1(ProjectList).

LOOP THROUGH WORKSHEETS
There are different ways to loop through the sheets: using indexnumbers, through the collection, using an array or the selected sheets. Loop using the codenames is too cumbersome in most of the cases to my sense.

1. loop using the index number (tabs from left to right including invisible tabs)
see code "view_123" from above

2. loop through the entire collection
you can use some code to exclude or include certain sheets (most of the times we use the names for that purpose)

Code:

Sub viewall_exceptone()
Dim sht As Worksheet

    For Each sht In Worksheets
        If LCase(sht.Name) <> "summary" Then
        MsgBox sht.Name
        End If
    Next sht

End Sub

3. loop through a predefined array of sheets
3A. using names

Code:

Sub operate_on_sheets()
'Erik Van Geit
'050910
'do some operations with certain sheets

Dim SheetsArray As Variant
Dim i As Integer
SheetsArray = Array("total", "report", "current month")

For i = 0 To UBound(SheetsArray)
Sheets(SheetsArray(i)).PrintOut Copies:=1
Next i

End Sub

3B. using indexes

Code:

Sub operate_on_sheets()
'Erik Van Geit
'050911
'do some operations with certain sheets

Dim SheetsArray As Variant
Dim i As Integer
SheetsArray = Array(5, 6, 9, 19)

For i = 0 To UBound(SheetsArray)
Sheets(SheetsArray(i)).Select '.PrintOut Copies:=1
Next i

End Sub

3C. the list can be taken from a sheet
instead of hardcoded within the macro

Code:

Sub operate_on_sheets()
Dim SheetsArray As Variant

SheetsArray = Sheets(4).Range("A1:A3")
Sheets(Application.Transpose(SheetsArray)).Select

End Sub

4. loop through selected sheets
barely used to my sense

Code:

Sub test()
Dim ws As Worksheet
Dim xlsheetArray() As Variant
Dim x As Integer
    
ReDim xlsheetArray(ActiveWindow.SelectedSheets.Count - 1)

    For Each ws In ActiveWindow.SelectedSheets
    xlsheetArray(x) = ws.Name
    x = x + 1
    Next ws

    For x = 0 To UBound(xlsheetArray)
    MsgBox xlsheetArray(x)
    Next x
    
End Sub

5. loop using codenames
displayed as an example, but not userfriendly to my sense
also this code is rather "stupid"

Code:

Sub ShowSheetCodenames()
Dim i As Integer

    For i = 1 To Sheets.Count
    MsgBox ActiveWorkbook.VBProject _
    .VBComponents("Sheet" & i).Properties("_CodeName")
    Next i

End Sub

EXTRAS
1. synchronise tab & codenames

Code:

Option Explicit

Sub sheetcodename_is_sheetname()
'Erik Van Geit
'060907
Dim sh As Worksheet
Dim msg As String

msg = "This code will replace all codenames of your sheets by the tabnames"
If MsgBox(msg, 36, "Replace codenames") = vbNo Then Exit Sub

msg = ""
On Error Resume Next

    For Each sh In Worksheets
    ActiveWorkbook.VBProject.VBComponents(sh.CodeName).Properties( _
    "_CodeName").Value = Application.Substitute(sh.Name, " ", "_")
        If Err Then
        Err.Clear
        msg = msg & vbLf & sh.Name
        End If
    Next sh
    
    If msg <> "" Then
    msg = "Some codenames could not be changed" & msg
    Workbooks.Add
    Range("A1") = msg
    MsgBox msg, 48, "ERROR REPORT"
    End If

End Sub

2. add sheet if it doesn't exist

Code:

Sub add_sheet()
Dim x As Worksheet
Dim sh_name As String

sh_name = "temp"
    
On Error Resume Next

    With ThisWorkbook
    Set x = .Sheets(sh_name)
        If Err = 9 Then
        .Worksheets.Add.Name = sh_name
        End If
    End With
    
On Error GoTo 0

End Sub

I hope this will be useful

best regards,
Erik

stanleydgromjr · Mar 21, 2007

Erik,

Thank you very much.

I have used a lot of your code to learn, and solve problems.

Have a great day,
Stan

erik.van.geit · Apr 14, 2007

If you want to loop through codenames, you can use something like

Code:

Sub test()
Dim i As Integer
Dim codeNm As String
Dim sh As Worksheet

    For i = 1 To 3
    codeNm = "cpt" & i
    Set sh = GetSheetRefByCodename(codeNm)
        If Not sh Is Nothing Then
        MsgBox "codename: " & codeNm & vbLf & _
        "tabname: " & sh.Name, 64, "SHEETNAME"
        Else
        MsgBox "this codename does not exist", 48, "ERROR"
        End If
    Next i

End Sub

Public Function GetSheetRefByCodename(CodeName As String) As Worksheet
Dim sh As Worksheet

    For Each sh In ThisWorkbook.Worksheets
    If sh.CodeName = CodeName Then Exit For
    Next
    
Set GetSheetRefByCodename = sh

End Function

as shown by RightClick in another thread

erik.van.geit · Apr 24, 2007

A common mistake often overlooked.

Code:

Range("A1").Select

You get an error 1004
application or object defined error
or
'method select of rangeclass failed
WHY ??

You need to understand this:
1.
In a sheetmodule ranges or objects which are not preceeded by their "parent" are considered to be on the "Me"sheet.
In normal modules they are considered to be on the Active Sheet.
2.
You can only cells which are on the active sheet.
You cannot select cells which are not on the active sheet.

EXAMPLE
sheetmodule sheet1
Range("A1") refers to Sheets("sheet1").Range("A1")
normal module
Range("A1") refers to ActiveSheet.Range("A1")

bugging CODESAMPLES
1.
ASSUMPTIONS
code in any sheetmodule - but not "Sheet2"
"Sheet2" exists

Code:

Sub test1()
Worksheets("Sheet2").Activate 
'error 1004 on next line
'application or object defined error
Range("A1").Select 
End Sub

2.
ASSUMPTIONS
code in any module
codenames Sheet1 and Sheet2 exist

Code:

Sub test2()
Dim c As Range

Set c = Sheet2.Range("A1")
Sheet1.Select
'error 1004
'method select of rangeclass failed
c.Select
End Sub

SOLUTION for ex. 1 & 2: use GOTO

Code:

Application.Goto Sheet2.Range("A1")

3.
this kinda errors is even more difficult to grasp in some situations like

Code:

Sub test3()
Sheet1.Select
    With Sheet2
    'method Range of object Worksheet failed
    .Range(Cells(1, 1), Cells(5, 3)) = "something"
    End With
End Sub

What's the matter here

You are trying to define a range on Sheet2. Cells(1, 1) & Cells(5, 3) are refering to ACTIVE sheet (Sheet1) and cannot be part of a range on Sheet2!!

SOLUTION
You need extra DOTs (before "Cells") to refer correctly to "With Sheet2"

Code:

Sub test4()
Sheet1.Select
    With Sheet2
    .Range(.Cells(1, 1), .Cells(5, 3)) = "something"
    End With
End Sub

May this be another useful step when refering to worksheets.

NOTE about SELECT
Ex. 1 & 2 assuming you "need" to "go to" somewhere, which is NOT necessary most of the time.
Ex. 3 using "select" only for testing purposes (to be sure the error is showing up

)

Risk · May 3, 2007

XL'lent. This is exactly what I needed.

Rowland Hamilton · Jul 28, 2011

Erik:

I can't seem to get this code to work. I received an 1004 error eventually.

I want to cycle through the worksheets in the workbook ("United States (de linked).xlsm")and

select only the ones that include "*-Line item*" which is preceded by a cc # and sometimes followed by an s as in "5049-Line Items"

Then copy a contiguous, but varing range of data from each sheet and past it to a master worksheet in the workbook named "Line items-Combined.xlsm" which contains the macro.

Sub Populate_line_item_workbooka()
Dim MasterWB As Workbook
Dim SourceWB As Workbook
Dim ws As Worksheet
Set MasterWB = Workbooks("Line items-Combined.xlsm")
Set SourceWB = Workbooks("United States (de linked).xlsm")
For Each ws In SourceWB.Worksheets
If ws.Name Like "*-Line item" Then
ws.Select
Range("A3").Select
Call Copy_move_line_items
Exit For
End If
Next ws
End Sub
Sub Copy_move_line_items()
'copy
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

'paste
Windows("Line items-Combined.xlsm").Activate
Sheets("Master-Incoming").Activate
Range("A65000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Thank you
Rowland

Rowland Hamilton · Jul 28, 2011

To clarify, I mean select the worksheets with tab names that include the phrase "Line Item"

WORKSHEETS INFO: reference, loop, add, etcetera

erik.van.geit

MrExcel MVP

Excel Facts

stanleydgromjr

Banned

erik.van.geit

MrExcel MVP

erik.van.geit

MrExcel MVP

Risk

Board Regular

Rowland Hamilton

Active Member

Rowland Hamilton

Active Member

Similar threads

Forum statistics

Share this page

WORKSHEETS INFO: reference, loop, add, etcetera

erik.van.geit

MrExcel MVP

Excel Facts

stanleydgromjr

Banned

erik.van.geit

MrExcel MVP

erik.van.geit

MrExcel MVP

Risk

Board Regular

Rowland Hamilton

Active Member

Rowland Hamilton

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock