麻烦在工作簿中移动Sheets [英] Trouble moving through Sheets in a workbook

问题描述

所以我正在编写一个宏来从多个工作簿中提取信息，然后编译成一个数组，最终以摘要报告的形式放入一个打开的工作簿中。在我尝试切换当前活动工作簿的工作表之前，宏的工作方式就像一个魅力。
这导致我无法使用最终的for循环来提取任何数据。

我有一个注意事项是本地的工作簿变量在整个宏中都是空的，这让我觉得它与我如何选择工作簿有关。此外，aCell变量显示为Nothing，即使最终for循环
正在运行。我刚刚开始使用VBA，所以请随意批评我的整体代码。代码如下。

Sub Open_Returns_Files（）



Dim fileCount As long

Dim filePath（）作为字符串

Dim pathCount As Long

ReDim filePath（0至35）

Dim i As Long

Dim j As Long

Dim k As Long

Dim l As long

Dim wbRtn As Workbook

Dim numRows As长

pathCount = 0

Dim info（）As String

Dim infoCount As Long

ReDim info（0至60000,0至7）
$
infoCount = 0

将aCell调暗为范围

MsgBox"选择要报告的返回文件"

使用Application.FileDialog（msoFileDialogOpen）

        .AllowMultiSelect = True

        。显示

For fileCount = 1 To .SelectedItems.Count

    filePath（fileCount - 1）= .SelectedItems（fileCount）

    pathCount = pathCount + 1

下一个fileCount

结束与





对于i = 0到pathCount - 1

设置wbRtn = Workbooks.Open（filePath（i），UpdateLinks：= 0）

表（ "数据"）。选择

numRows = WorksheetFunction.CountA（范围（"A1"）。EntireColumn）

对于j = 2到numRows

   对于k = 0到6

        info（infoCount，k）= Cells（j，k + 1）

   下一个k

    infoCount = infoCount + 1

下一页j

ActiveWorkbook.Sheets（"Refund Paste Values This Tab"）。选择

'循环问题如下！*！*！*！

对于l = 0到numRows - 2

   对于每个范围内的aCell（ActiveSheet.Cells（2,3），ActiveSheet.Cells（numRows，3））

       如果信息（（infoCount - l），1）= aCell.Value则为
            info（（infoCount - l），7）= aCell.Offset（0,3）.Value

       结束如果是
   下一个aCell

下一个l



wbRtn.Close SaveChanges：= False

下一个i



结束子

解决方案

我会告知一个或两件事......

1.当您通过VBA / UI打开工作簿时，它将成为ActiveWorkbook，如果没有明确地取消或关闭它将保留。如果关闭则工作簿是在打开本书之前激活将再次激活。

2.如果没有绝对引用，任何范围将指示到activesheet.So范围（"A1"）将引用活动表的A1。和工作表（"MyName"）。范围（"A1"）将向MyName工作表的A1指示。

3.同样，如果任何工作表未被绝对引用，则表示activeworkbook.So Activesheet将指示activeworkbook和Workbooks（2）.ActiveSHeet将参考第二个pened工作簿的活动表。

希望你能找到你需要改变的行....... / p>

一切顺利......

So I am writing a macro to pull info from several workbooks and then compile into one array to eventually be put in an open workbook in the form of a summary report. The macro works like a charm until I attempt to switch the sheet of the current active workbook. This results in me being unable to pull any data with the final for loop.

One note I have is that the workbook variable in locals is empty the entire way through the macro, which makes me think it has something to do with how i am selecting the workbooks. Also, the aCell variable shows as Nothing, even when the final for loop is running. I am just getting started with VBA, so please feel free to critique my overall code. Code below.

Sub Open_Returns_Files()

Dim fileCount As Long
Dim filePath() As String
Dim pathCount As Long
ReDim filePath(0 To 35)
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim wbRtn As Workbook
Dim numRows As Long
pathCount = 0
Dim info() As String
Dim infoCount As Long
ReDim info(0 To 60000, 0 To 7)
infoCount = 0
Dim aCell As Range
MsgBox "Select Return Files To Report"
With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Show
For fileCount = 1 To .SelectedItems.Count
    filePath(fileCount - 1) = .SelectedItems(fileCount)
    pathCount = pathCount + 1
Next fileCount
End With



For i = 0 To pathCount - 1
Set wbRtn = Workbooks.Open(filePath(i), UpdateLinks:=0)
Sheets("Data").Select
numRows = WorksheetFunction.CountA(Range("A1").EntireColumn)
For j = 2 To numRows
    For k = 0 To 6
        info(infoCount, k) = Cells(j, k + 1)
    Next k
    infoCount = infoCount + 1
Next j
ActiveWorkbook.Sheets("Refund Paste Values This Tab").Select

'Loop with issues below !*!*!*!

For l = 0 To numRows - 2
    For Each aCell In Range(ActiveSheet.Cells(2, 3), ActiveSheet.Cells(numRows, 3))
        If info((infoCount - l), 1) = aCell.Value Then
            info((infoCount - l), 7) = aCell.Offset(0, 3).Value
        End If
    Next aCell
Next l

wbRtn.Close SaveChanges:=False
Next i

End Sub

解决方案

I will inform one or two thing....

1.When you open a workbook by VBA/UI it will become ActiveWorkbook and it will remain if not specifically dectaivated or closed.If closed then the workbook which was active before opening this book will become active again.

2.Any range if not absolutely referred,will indicate to activesheet.So Range("A1") will refer to A1 of activesheet.And Worksheets("MyName").Range("A1") will indicate to A1 of MyName sheet.

3.Similarly if Any sheet is not absolutely referred,will indicate to activeworkbook.So Activesheet will indicate to activeworkbook and Workbooks(2).ActiveSHeet will refer to activesheet of 2nd pened workbook.

Hope you can find the line where you need change....

All the best...

这篇关于麻烦在工作簿中移动Sheets的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！