CODEWITHSUNDEEP
excelvba
Steve Vitale
Steve Vitale

Reputation: 25

How can I fix this macro to work between two workbooks?

My code is working when I just use a single workbook and communicate between sheets but gives me subscript out of range errors and object not defined errors when I attempt to reference a cell range in a sheet contained in a different work book. Right now, the error is occurring at "Set pidat = Worksheets("pidat")

 Dim pival As Double
'Dim eom As Worksheet 'declaring pidat worksheet as variable
'Set eom = Worksheets("EOM") 'declaring eom worksheet as variable
'Set Inv_Level = Worksheets("Inv_Levels")

Dim pidat As Worksheet                       'declaring eom worksheet as variable
Set pidat = Worksheets("pidat")
Dim steve As Workbook
Set steve = Application.Workbooks("EOM Report VBA")
Dim EOMAs As Workbook
Set EOMAs = Application.Workbooks("EOMA")
Dim Inv_Level As Worksheet






'These changes allow for a dynamic range to be referenced outside of the active sheet/workbook

Dim location As String
Dim rownum As Long
Dim loopy As Long
Dim fRng As Range
Dim J As Long
Dim rn As Date
Dim last As Date
Dim rnm As Integer
Dim lastm As Integer
Dim tyear As Long
Dim K As Long



With pidat
    J = .Range("J2").Value
    rn = Now
    last = .Range("B1").Value
    rnm = month(rn)
    lastm = month(last)
    tyear = year(rn)

    If lastm < rnm Then
        .Range("B1") = (rnm & "/" & "01" & "/" & tyear & " 07:30")
        J = J + 100
        .Range("J2") = J

    End If
End With



K = J + 100

'names of workbook/sheet referenced


With steve

    rownum = .Range("E" & Rows.Count).End(xlUp).Row 'counts the number of rows in the location tag column

    For loopy = 3 To rownum                  'Data values start after row 3, loops through each row in the column

        If .Range("E" & loopy) <> "" Then

            location = .Range("E" & loopy)
            'newloc = location
            With Inv_Level

                Set fRng = .Cells.Range("A" & J, "ZZ" & K).Find(What:=location, LookIn:=xlFormulas, LookAt:=xlPart) 'eom can be any sheet you need to perform the .Find again
            End With

            If Not fRng Is Nothing Then
                fRng.Offset(0, -1) = pidat.Range("D" & loopy)
            Else: End If
            'if the search item is not found, do nothing, go to next loop
        End If




    Next loopy

End With


End Sub

Upvotes: 0

Views: 29

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

You need to qualify the specific workbook you want to work with.

The line Set pidat = Worksheets("pidat") will fail if the active workbook at the time this line is executed has no worksheet named pidat.

Here is an example of how to qualify a workbook

Dim theWorkbook as Workbook
Set theWorkbook = Application.Workbooks("myWorkbook")

Dim pidat as Worksheet
Set pidat = theWorkbook.Worksheets("pidat")

You could go one step further and verify that a sheet named pidat (or whatever) exists in the qualified workbook, but I'll leave you to discover how to do that :)

Upvotes: 1

Related Questions