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
– Damian, Commented Oct 23, 2019 at 9:14

JvdV · Accepted Answer · 2019-10-23 09:23:36Z

2

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

answered Oct 23, 2019 at 9:23

JvdV

76.8k8 gold badges48 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Damian · Accepted Answer · 2019-10-23 09:33:56Z

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, 2019 at 9:33

answered Oct 23, 2019 at 9:25

Damian

5,1741 gold badge12 silver badges22 bronze badges

4 Comments

JvdV Over a year ago

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 =)

Damian Over a year ago

@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?

JvdV Over a year ago

First line in his Do While loop says otherwise Do While ActiveSheet.Index < Worksheets.Count

Damian Over a year ago

Edited so it skips the last worksheet, thanks @JvdV!

Collectives™ on Stack Overflow

Excel vba - select first column

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related