CODEWITHSUNDEEP
vbaexcelset
Sarai
Sarai

Reputation: 3

set workbooks and worksheets returns either "nothing" or 424 run-time error object required

I am desperately hoping someone can help me. I've been troubleshooting this issue for the last 3 days non-stop. I am new to VBA so I am sure it has to be something elementary. Basically I have 2 open workbooks, one holds the data, and the other is where I want to paste some of the data based on an "if, then". For some reason, I cannot set the workbooks as variables in order to easily reference/update. I know we will be changing the name of these 2 documents as soon as I am able to go live, so I wanted to just change it in one place instead of all throughout the code. Below is the code, can someone please tell me what I'm missing? The comments are included to help make the picture clear. I look forward to hearing any wisdom that you could impart?

Dim I As Integer

'NAMING AND ASSIGNING TYPE WORKBOOKS AND WORKSHEETS FOR EASIER REFERENCE
Dim wbraw As Workbook
Dim wsrawwires As Worksheet
Dim wbdest As Workbook
Dim wsdestwires As Worksheet
Dim wsdestcover As Worksheet

'SETTING LOCATION OF NAMED WORKBOOKS AND WORKSHEETS FOR CODING
Set wbraw = Workbooks("scorecard (raw data) revised.xlsx")
Set wbdest = Workbooks("scorecard revised.xlsm")
Set wsrawwires = wbraw.Sheets("wires")
Set wsdestwires = ThisWorkbook.Sheets("sheet2")
Set wsdestcover = ThisWorkbook.Sheets("cover")

'NAMING AND LOCATING VARIABLE FOR CONDITION OF IF STATEMENT
previousyear = Workbooks("scorecard revised.xlsm").Range("x10")

'NAMING AND LOCATING VARIABLE FOR SOURCE TESTED IN IF STATEMENT
rawwiresfinalrow = wsrawwires.Range("b537").End(xlUp).Row

For I = 3 To rawwiresfinalrow
'CODE FOR PULLING APPLICABLE DATA INTO DESTINATION REPORT
    If wsrawwires.Range(Cells(I, 5)).Value = previousyear.Value Then
        wsrawwires.Range(Cells(I, 2), Cells(I, 5)).Copy
        wsdestwires.Range("a1").PasteSpecial xlPasteValues
    End If
Next I

Upvotes: 0

Views: 96

Answers (1)

MGP
MGP

Reputation: 2551

First things first, I would always recommend using Option Explicitwhich basically stops the code from running if not all variables are defined. This makes debugging easier and you will find typos straight away.

So use this instead:

Option Explicit

Sub nameOfSub()

Dim i As Long 'use Long instead of Integer.
Dim rawwiresfinalrow as Long
Dim counter As Long

'NAMING AND ASSIGNING TYPE WORKBOOKS AND WORKSHEETS FOR EASIER REFERENCE
Dim wbraw As Workbook
Dim wsrawwires As Worksheet
Dim wbdest As Workbook
Dim wsdestwires As Worksheet
Dim wsdestcover As Worksheet

Dim previousyear As Range

'SETTING LOCATION OF NAMED WORKBOOKS AND WORKSHEETS FOR CODING
Set wbraw = Workbooks("scorecard (raw data) revised.xlsx")
Set wbdest = Workbooks("scorecard revised.xlsm")
Set wsrawwires = wbraw.Sheets("wires")
Set wsdestwires = ThisWorkbook.Sheets("sheet2")
Set wsdestcover = ThisWorkbook.Sheets("cover")

'NAMING AND LOCATING VARIABLE FOR CONDITION OF IF STATEMENT
Set previousyear = wbdest.Sheets("Enter_your_sheetname_here").Range("x10")

'NAMING AND LOCATING VARIABLE FOR SOURCE TESTED IN IF STATEMENT
rawwiresfinalrow = wsrawwires.Range("b537").End(xlUp).Row

counter = 1
For i = 3 To rawwiresfinalrow
'CODE FOR PULLING APPLICABLE DATA INTO DESTINATION REPORT
    If wsrawwires.Cells(I, 5).Value = previousyear.Value Then
        With wsrawwires
            .Range(.Cells(i, 2), .Cells(i, 5)).Copy
        End With
        wsdestwires.Range("A" & counter).PasteSpecial xlPasteValues
        counter = counter + 1
    End If
Next I

End Sub

Upvotes: 2

Related Questions