Excel vba - select first column

Question

i'm trying to make some changes in excel file using VBA, the file contains many sheets the code should make changes for 1st sheet then go to the next and next, but after makes the changes in 1st sheet and go to 2nd it shows:

Error no 1004 "Object error".

Here the code:

Sub AddRefNo()

    'This code adds Reference Number to All BOQ sheets based on Worksheet Name
    'select the first sheet
    Worksheets(4).Select

    ' Work in One Sheet
    Do While ActiveSheet.Index < Worksheets.Count

        'add new Column
        'the error happens here
        Columns("A:A").Select

        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "Ref. No"
        Range("A2").Select

        'Find Sheet Name
        MySheet = ActiveSheet.Name

        'creat numbering system
        Dim Noe As String
        Noe = 0

        ' Find the last row
        Dim LastRow As Integer

        LastRow = Range("E" & Rows.Count).End(xlUp).Row
        Range("E2").Select

        'repeat steps to the last row

        Do While ActiveCell.Row < LastRow

            'checking if the cell is not blank

            Do While ActiveCell.Value <> ""

                 ActiveCell.Offset(0, -4).Select
                 Noe = Noe + 1
                 ActiveCell.Value = MySheet & " - " & Noe
                 ActiveCell.Offset(0, 4).Select
                 ActiveCell.Offset(1, 0).Select

            Loop

            ActiveCell.Offset(1, 0).Select

        Loop

        Noe = 0
        Range("A1").Select
        ActiveSheet.Next.Select

    Loop

    Worksheets(1).Select

End Sub

What is the purpose of this code? Add a column in every sheet which will contain the sheet name + a counter? — Damian, Oct 23 '19 at 09:14
[How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/9758194) — JvdV, Oct 23 '19 at 09:14

score 2 · Answer 1 · answered Oct 23 '19 at 09:23

Here is a way to reliable loop through your worksheet index numbers:

Sub AddRefNo()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim x As Long

For x = 4 To wb.Worksheets.Count - 1
    Set ws = wb.Worksheets(x)
    'Your code to work with ws as a parent
Next x

End Sub

Damian · Answer 2 · 2019-10-23T09:33:56.643

2

This should do the trick if you want to loop from sheet 4:

Option Explicit
Sub AddRefNo()

    'Declare a worksheet variable
    Dim ws As Worksheet
    'Loop every sheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        If ws.Index < 4  Or ws.Index = ThisWorkbook.Worksheets.Count Then GoTo nextWorksheet
        'Reference always the sheet
        With ws
            'Calculate last row
            Dim LastRow As Long
            LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
            'Insert a column
            .Range("A:A").Insert
            .Range("A1") = "Ref. No"
            'Put the name sheet + reference starting from 1
            With .Range("A2:A" & LastRow)
                .FormulaR1C1 = "=" & Chr(34) & ws.Name & Chr(34) & "&ROW(RC)-1"
                .Value = .Value
            End With
        End With
nextWorksheet:
    Next ws

End Sub

edited Oct 23 '19 at 09:33

answered Oct 23 '19 at 09:25

Damian

5,152
1
10
21

OP seems to be interested in index from 4 to 1 below the total worksheet count though. Upvoted either way for a more complete answer than mine =) – JvdV Oct 23 '19 at 09:29
@JvdV `ActiveSheet.Next.Select` this is supposed to select the next worksheet, right? If he is starting from sheet 4 and selecting the next in every loop... this will go to the `worksheets.count` right? – Damian Oct 23 '19 at 09:31
First line in his `Do While` loop says otherwise `Do While ActiveSheet.Index < Worksheets.Count` – JvdV Oct 23 '19 at 09:31
1

Edited so it skips the last worksheet, thanks @JvdV! – Damian Oct 23 '19 at 09:34

Excel vba - select first column

2 Answers2