根据2个标准输入将行从一个excel表复制到另一个excel表

Question

我有一个excel工作表，其中第一个工作表包含大约500行的所有主数据，并一直延伸到R列，这个工作表被称为总体工作表。O列包括一个月，P列包括一年。

我有另一张表，我希望在其中复制数据，这被称为"预测月"。在B1的顶部，选择了我想要复制的月份，在D1中，选择了年份。我希望按钮读取这两个单元格，并在此基础上从"整体表"中复制数据。

我已经编写了如下所示的代码，但由于某种原因，在添加下一个数据之前，数据被输入"预测月"10次(也是10次(。我应该在这张表中只有3条数据，但每个数据有30、10条。

此外，每张纸的前3行都有标题，因此数据应该开始写入第4行(确实如此(

有人能帮忙吗？？

Private Sub CommandButton1_Click()
Dim month As String
Dim year As String
Dim c As Range
Dim d As Range
Dim k As Integer
Dim source As Worksheet
Dim targetforecastmonth As Worksheet
'change worksheet designations as needed
Set source = ActiveWorkbook.Worksheets("Overall Sheet")
Set targetforecastmonth = ActiveWorkbook.Worksheets("Forecast Month")
targetforecastmonth.Range("A4:Z1000").Clear
month = ActiveWorkbook.Worksheets("Forecast Month").Range("B1")
year = ActiveWorkbook.Worksheets("Forecast Month").Range("D1")
k = 4
For Each c In source.Range("O4:O1000")
For Each d In source.Range("P4:P1000")
If c = month And d = year Then
source.Rows(c.Row).Copy targetforecastmonth.Rows(k)
k = k + 1
End If
Next d
Next c
End Sub

Answer 1

这似乎是错误的逻辑。我想你需要Expl.：O8、P8与B1、D1匹配所以你只需要一个周期：

For Each c In source.Range("O4:O1000")
d = source.Range("P" & k)
If c = month And d = year Then
source.Rows(c.Row).Copy targetforecastmonth.Rows(k)
End If
k = k + 1
Next c

Answer 2

试试这个，我希望这会有所帮助。

Private Sub CommandButton1_Click()
Dim month As String
Dim year As String
Dim c As Range
Dim k As Integer
Dim source As Worksheet
Dim targetforecastmonth As Worksheet
'change worksheet designations as needed
Set source = ActiveWorkbook.Worksheets("Overall Sheet")
Set targetforecastmonth = ActiveWorkbook.Worksheets("Forecast Month")
targetforecastmonth.Range("A4:Z1000").Clear
month = ActiveWorkbook.Worksheets("Forecast Month").Range("B1")
year = ActiveWorkbook.Worksheets("Forecast Month").Range("D1")
k = 4
For Each c In source.Range("O4:O1000")
If c = month And source.Cells(c.Row, 16).Value = year Then
source.Rows(c.Row).Copy targetforecastmonth.Rows(k)
k = k + 1
End If
Next c
End Sub

Answer 3

您有一个嵌套的For Each循环，这意味着您先获取单元格"O4"，然后循环通过单元格"P4:P1000"，然后再移动到单元格"O5"，再循环通过单元格"P4:P1000"，以此类推。。。例如，如果"O4"的单元格值满足month标准，则每当循环通过列P找到满足year标准的单元格时，就会复制并粘贴行号4。试试这个：

Private Sub CommandButton1_Click()
Dim month As String
Dim year As String
Dim c As Range
Dim d As Range
Dim x As Long
Dim k As Integer
Dim source As Worksheet
Dim targetforecastmonth As Worksheet
'change worksheet designations as needed
Set source = ActiveWorkbook.Worksheets("Overall Sheet")
Set targetforecastmonth = ActiveWorkbook.Worksheets("Forecast Month")
targetforecastmonth.Range("A4:Z1000").Clear
month = ActiveWorkbook.Worksheets("Forecast Month").Range("B1")
year = ActiveWorkbook.Worksheets("Forecast Month").Range("D1")
k = 4
x = 4
For Each c In source.Range("O4:O1000")
Set d = source.Range("P" & x)
If c.Value = month And d.Value = year Then
source.Rows(c.Row).Copy targetforecastmonth.Rows(k)
k = k + 1
End If
x = x + 1
Next c
End Sub