时间戳

首页 > 解决方案 > 如何修复此宏以在两个工作簿之间工作？

excel - 如何修复此宏以在两个工作簿之间工作？

问题描述

当我只使用单个工作簿并在工作表之间进行通信时，我的代码正在工作，但是当我尝试引用不同工作簿中包含的工作表中的单元格区域时，给出了下标超出范围错误和对象未定义错误。现在，错误发生在“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

标签： excelvba

解决方案

您需要限定要使用的特定工作簿。

Set pidat = Worksheets("pidat")如果执行此行时的活动工作簿没有名为 的工作表，则该行将失败pidat。

以下是如何限定工作簿的示例

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

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

您可以更进一步并验证pidat (or whatever)合格工作簿中是否存在名为的工作表，但我会让您发现如何做到这一点:)

推荐阅读