Why do I get object required error for the below VBA code?

Question

I am new to VBA, I need help applying the below VBA code to two specific work sheets by Region and by Model. The code simply finds the last column which has the name total for the year and copies the previous months values into a new column. But I get an error object required at ws.

   Sub Insert_New_Col()

Dim Found As Range, BeforeR As Long
Dim xSheets As Variant
Dim ws As Worksheet
Dim i As Long

xSheets = Array("By Region", "By Model") '

For i = LBound(xSheets) To UBound(xSheets)

    Set ws = xSheets(i)
    Set Found = ws.Rows(3).Find(What:="Total for the Year", Lookat:=xlWhole)
    BeforeR = R.Column - 1

    If Found Is Nothing Then
        MsgBox ("The word 'Totals' was not found in Row 5 on Sheet: " & ws.Name)
    Else
        Columns(BeforeR).Copy
        ws.Columns(R.Column).Insert Shift:=xlRight
    End If
Next i

End Sub

BigBen · Accepted Answer · 2019-12-17 18:41:23Z

1

You want an actual member of the Worksheets collection:

Set ws = ThisWorkbook.Worksheets(xSheets(i))

BeforeR = R.Column - 1

What is R? Add Option Explicit to the top of the module and declare all variables.

answered Dec 17, 2019 at 18:41

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

2 Comments

user12549061 Over a year ago

The code works fine if executed from the VBA Window or from any of the two sheets. I have the command button on an input sheet, when I run the code it just copies the cells in the input sheet and pastes it to the region and model sheets. How do I fix this so it only copies from the region and model sheets?

BigBen Over a year ago

You need to qualify which sheet Columns(BeforeR) is on.

Collectives™ on Stack Overflow

Why do I get object required error for the below VBA code?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related