object required error when running macro from personal macro wkb

Question

Public Sub FiltroPro()

Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim Pac1 As String
Dim Pac2 As String
Dim Pac3 As String

Dim finalrow As Long
Dim i As Long

ActiveWorkbook.Activate

Set datasheet = sheet10


Set reportsheet = Hoja4


reportsheet.Range("A9:L1000").ClearContents

datasheet.Select

finalrow = datasheet.Cells(datasheet.Rows.Count, "A").End(xlUp).Row

Pac1 = reportsheet.Cells(1, 2).Value
Pac2 = reportsheet.Cells(2, 2).Value
Pac3 = reportsheet.Cells(3, 2).Value

For i = 2 To finalrow

    If Cells(i, 2) = Pac1 Or Cells(i, 2) = Pac2 Or Cells(i, 2) = Pac3 Then
    Range(Cells(i, 1), Cells(i, 12)).Copy
    reportsheet.Select
    Range("A1000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
    datasheet.Select
    End If
    
Next i

reportsheet.Select

Range("B2").Select

MsgBox ("busqueda finalizada")



End Sub

This macro is supposed to search for names on a patient datasheet and copy and paste results on a reportsheet on the same workbook. It brings an error on line set datasheet = sheet10 (object required ) when I run it from my personal macro wkb

That means that the personal workbook doesn't have a sheet with a codename of `sheet10`. If you want to reference a sheet that's not in `ThisWorkbook` by its codename, see [this](https://stackoverflow.com/questions/25203173/fully-reference-a-worksheet-by-codename) — BigBen, Aug 06 '20 at 17:35

score 0 · Answer 1 · answered Aug 06 '20 at 17:52

There are different ways to access the sheets of a workbook. One is to use the Worksheets-collection of a Workbook (or the Sheets-collection). This can be done either by using the index or the sheet name. Assuming you are working on the ActiveWorkbook (the workbook that is currently shown and has the focus), you can use

Set datasheet = ActiveWorkbook.Worksheets(1)  ' Access the first sheet 
Set datasheet = ActiveWorkbook.Worksheets("Sheet10")  ' Access the sheet with the name Sheet10

The name of a sheet is the name displayed in Excel

The second way is to access it via it's codename. Per default, the codename of a sheet is the same as the name, however it will not change when you rename a sheet. You can see and change the codename of a sheet only from the VBA environment - in the project window and in the property window.

You can access the sheet by using the codename as an object variable - like you do in your code.

Set datasheet = sheet10

However, this is only available as long as your code is stored within that workbook. If your code is outside of the workbook (eg in the personal workbook), it is unknown and leads to a compiler error.

TL;DR: You are good probably just using ActiveWorkbook.Worksheets("Sheet10")

object required error when running macro from personal macro wkb

1 Answers1