Excel VBA For-Next循环将数据从一个WB提取到另一个 [英] Excel VBA For-Next Loop to extract data from one WB to another

问题描述

我正在研究一个for循环，该循环基于第12列中等于机票"的字符串提取整个数据行.

I am working on a for loop that extracts entire rows of data based on the string in the 12th column being equal to "Airfare."

该想法是复制第12列(EXPENSE_TYPE)为机票的数据行，并将其粘贴到第二个工作簿中.

The idea is to copy the rows of data where column 12 (EXPENSE_TYPE) is Airfare and paste it into a second workbook.

下面的我的代码未正确遍历所有120行数据.当我运行宏时，它仅提取满足我的条件的第一行数据.如果您能找到我的问题，请告诉我.谢谢！

My code, below, is not properly looping through all 120 rows of data. When I run my macro, it only extracts the first row of data where my criteria is met. Let me know if you can find my issue. Thanks!

Sub exportDataToOtherWorkbook()
Dim lastRow As Long
Dim i As Long
Dim p As Integer
Dim q As Integer
Dim erow As Long

lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow
    If Cells(i, 12) = "Airfare" Then
    Range(Cells(i, 1), Cells(i, 16)).Select
    Selection.Copy


Workbooks.Open Filename:="C:\users\andrew.godish\Desktop\Practice Files\masterPracticeExtractDataWBtoWB.xlsx"

p = Worksheets.Count
    For q = 1 To p
        If ActiveWorkbook.Worksheets(q).Name = "Sheet2" Then
        Worksheets("Sheet2").Select
        End If
    Next q

    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    ActiveSheet.Cells(erow, 1).Select
    ActiveSheet.Paste
    ActiveWorkbook.Save
    Application.CutCopyMode = False
    End If
Next i
End Sub

推荐答案

我建议一种遍历每一行的替代方法.循环效率很低，应尽可能避免.

I would suggest an alternative to looping through each row. Loops are very inefficient and should be avoided if possible.

假设您的数据存储在要复制的工作簿的"Sheet1"(满足您的要求的更改)上，则可以过滤第12列，并使用如下更简单的命令复制所有数据:

Assuming your data is stored on "Sheet1" (change to meet your requirements) of the workbook you are copying from, you can filter column 12 and copy all of the data in a more simple command like this:

Sub Test()

        'Declare source and destination variables
        Dim sourceWB As Workbook, destWB As Workbook

        Set sourceWB = ThisWorkbook

        'Open the workbook to copy the data TO
        Workbooks.Open Filename:="C:\users\andrew.godish\Desktop\Practice Files\masterPracticeExtractDataWBtoWB.xlsx"
        Set destWB = ActiveWorkbook

        sourceWB.Sheets("Sheet1").Range("A1:P1").AutoFilter Field:=12, Criteria1:="Airfare"

        'The first offset on the copy is to avoid copying the headers each time, the second offset is to find the last row in the
        'list then offset to the next row. Copies data to new workbook
        sourceWB.Sheets("Sheet1").AutoFilter.Range.Offset(1).Copy Destination:=destWB.Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

        destWB.Save

        'Clear the filter from the source worksheet
        If sourceWB.Sheets("Sheet1").AutoFilterMode Then sourceWB.Sheets("Sheet1").ShowAllData

End Sub

我知道这不能直接回答您的问题，但是我认为这可能是一种更简单，更不易出错的方法.

I know this doesn't directly answer your question, but I think this may be an easier, less error-prone method.

因此，此方法遵循以下步骤:

So this method follows these steps:

• 打开目标工作簿
• 在第12栏中为机票"过滤Sheet1(请确保在必要时更改Sheet1)
• 将过滤后的范围复制并粘贴到目标工作表和工作簿中
• 删除应用于源工作表中第12列的过滤器

• Open the destination workbook
• Filter Sheet1 on column 12 for "Airfare" (be sure to change Sheet1 if necessary)
• Copy and paste the filtered range to the destination worksheet and workbook
• Remove the filter applied to column 12 in the source worksheet

令人困惑的部分可能是使用Offset(1).我在副本上使用它来避免复制列标题(它会使复制区域向下偏移一行).我在目的地使用它以避免覆盖最后一行(我们必须找到最后使用的行，然后向下递增一行).

The confusing part may be the use of Offset(1). I use that on the copy to avoid copying the column headers (it offsets the copy area by one row down). I use it on the destination to avoid overwriting the last row (we must find the last used row, then increment down one row).

这篇关于Excel VBA For-Next循环将数据从一个WB提取到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！