CODEWITHSUNDEEP
vbaexcel
Ruzaini Subri
Ruzaini Subri

Reputation: 77

Dim a sheet from variable workbook

Need you help on my macro

I try to vlookup from 2 difference workbook. 1 of my workbook will change name everyday as per date. I already get that part. Now I stuck how to Dim the variable workbook to use in vlookup formula. Here my code I want to dim OCBReport.

Sub Part_ETA_PLANNER()
'
'Part ETA PLANNER Macro
'

    '
    'Find OCB PLanner Today
    Dim OCBDaily As Workbook
    Dim t As Workbook

    For Each t In Workbooks
        If Left(t.Name, 11) = "OCB_Report_" Then
            Set OCBDaily = Workbooks(t.Name)
        End If
    Next t

    'Variable Dim
    Dim PartNumber, myRange As Long
    Dim OCBReport As Sheets

    Set OCBReport = "[ & OCBDaily & ]OCB" ' I got error on this part'
    PartNumber = Range("L2").Offset(0, -10).Address(0, 0)
    myRange = "'" & OCBReport & "'!C:W"

    'Vlookup Part ETA planner
    Dim LastRow  As Long
    LastRow = Sheets("Unfulfilled Daily Report").Range("E" & Rows.Count).End(xlUp).Row
    Sheets("Unfulfilled Daily Report").Range("L2").Formula = "=VLOOKUP(" & PartNumber & "," & myRange & ", 21, FALSE)"
    Sheets("Unfulfilled Daily Report").Range("L2").AutoFill Destination:=Range("L2:L" & LastRow)
    Sheets("Unfulfilled Daily Report").Range("L2:L" & LastRow).Copy
    Sheets("Unfulfilled Daily Report").Range("L2:L" & LastRow).PasteSpecial xlPasteValues
    Range("B2").Select
End Sub

Upvotes: 0

Views: 212

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149287

What you need is a String variable. Also if you put a variable within quotes then it will behave like a string. Also OCBDaily.Name will give you the workbook name which you can encase in "[]"

Change

Dim OCBReport As Sheets
Set OCBReport = "[ & OCBDaily & ]OCB" ' I got error on this part'

to

Dim OCBReport As String
OCBReport = "[" & OCBDaily.Name & "]OCB"

Upvotes: 1

Krossi
Krossi

Reputation: 81

If I understand your part of code correctly, you need to change it to:

Set OCBReport = OCBDaily.worksheets("OCB")

Your attempt tries to set the sheet variable to a string, which give should give an type error. 'OCB' should be the name of the wanted worksheet.

Greetings, Krossi

Upvotes: 0

Related Questions