VBA根据过滤器进行复制和粘贴 [英] VBA to copy and paste based on filter
问题描述
我正在寻找一个将过滤/验证数量的VBA脚本（列S）列Z并根据在G列中选择的类别从新PO选项卡扩展成本，并将其粘贴到正在写入订单的正确月份的PO选项卡中，该单元由单元格T12中的开始日期确定在新的PO选项卡上。另外一个挑战是，当类别从00变为01时，它应该下降到PO选项卡上的下一行并更改类别。
这里是在两个标签的屏幕截图中进行讨论，并再次感谢任何关于如何使这项工作的洞察力。
EDITED 6/7
Sub $ D $（$）
Dim Count，Qty As Long
Dim CatRng，MonthRng，SDate，CxlDate，PoNumb，Vendor As Range
Dim Total As Currency
Dim StrTarget As String
Dim Row，PORow，Col As Integer
Set CatRng = Sheets（NEW PO）。Range （G20：G43）
设置MonthRng =表（POs）范围（L122：W122）
StrTarget =表（新PO）范围（V12）
设置SDate =表（新PO）范围（T12）
设置CxlDat e =表（新PO）范围（T13）
设置PoNumb =表（新PO）范围（N10）
设置供应商=表（新PO ）.Range（D14）
计数= 0
对于Count = 0 To 99
总计= 0
数量= 0
'每当猫变化时，值都会重置
对于每个单元格，在CatRng
'中获取行号然后总共所需信息
如果单元格.Value = Count然后
Row = cell.Row
数量=数量+表（新PO）。范围（S&行）.Value
总计=总计+表格（新PO）范围（Z&行）.Value
'我猜只有ext的总成本在底部，
'这很容易改变，但
结束如果
下一个单元格
'现在，只有当有一个项目数量时，总计才能到达PO
如果数量> 0然后
PORow = Sheets（POs）。Range（K1048576）。End（xlUp）.Row + 1
'我会让您对PO号码和其他领域，但主要3在
之下完成与表（PO）
.Range（I& PORow）.Value =数量
.Range（K& PORow）.Value = Count
.Range（C& PORow）.Value = SDate
.Range（D& PORow）.Value = CxlDate
.Range（ B& PORow）.Value = PoNumb
.Range（F& PORow）.Value = Vendor
'我的理解是，T12中的目标月份与
'预计收货月份，我希望这是你正在寻找
对于每个单元格在MonthRng
如果cell.Value = StrTarget然后
Col =单元格。列
.Cells（PORow，Col）.Value =总
'这里使用的.cells列和列现在是整数
'（只有我可以永远
如果
下一个单元格
结束
结束如果
下一个计数
End Sub
为了适应你想要做的事情，请让我知道，如果没有。
这是一个很长的时间，因为我只是在会议之间一起敲打，工作。您可以使用行和列索引来操纵您希望拉入的任何其他数据，或修改您想要总计的费用。
Sub Test（）
Dim Count，Qty As Long
Dim CatRng，MonthRng As Range
Dim Total As Currency
Dim Row，PORow，Col As整数
设置CatRng =表（NEW PO）范围（G19：G43）
设置MonthRng =表（POs）范围（L122 ：W122）
计数= 0
对于Count = 0 To 99
总计= 0
数量= 0
'所以每次猫变化时，值都会重置
对于每个单元格在CatRng
'要获取行号，然后总共所需的信息
如果Cell.Value = Count然后
Row = Cell.Row
Qty =数量+表（NEW PO）。范围（T&行）.Value
总计=总计+ ）.Range（AA& Row）.Value
'我猜到ext的成本因为它总共在底部，
'这很容易改变虽然
结束如果
下一个单元格
'现在把总计一个PO只有有一个项目数量
如果数量> 0然后
PORow = Sheets（POs）。Range（J1048576）。End（xlUp）.Row + 1
'我会让你排序PO号码和其他字段，但主要3是在
下面使用表（PO）
.Range（I& PORow）.Value =数量
.Range（J& POROW）.Value = Count
'我的理解是，T12中的目标月份与预期收货月份的
格式相同，希望这是您要查找的
每个单元格在MonthRng
如果Cell.Value = .Range（T12）。然后
Col = Cell.Column
.Cells（PORow，Col）.Value =总
'这里使用的.cells列和列现在是整数
'（只有我可以得到它的工作）
结束如果
下一个单元格
结束
结束如果
下一个计数
结束子
Appreciate anyone's help creating the VBA code to execute a copy and paste function base upon certain criteria being selected. I think it needs to be a VBA script because I have a macro to clear the data in the New PO's tab once an "order" is written and the information is copied to the PO's tab. I currently have a copy and paste script which works perfect and copies 3 cells from the New PO tab to the POs tab, see below script. This is being executed by a button to "Copy the Data".
I am now looking for a VBA script which will filter/validate the quantity (column S) Column Z and extended cost from the NEW PO tab based on the category selected in Column G and paste that to the PO's tab under the correct month the order is being written, which is determined by the start date in cell T12 on the New PO tab. The additional challenge is that when the category changes for example from 00 to 01, it should drop to the next row on the PO's tab and change category as well.
Here are screen shots of the two tabs in discussion and again appreciate any insight into how to make this work.
EDITED 6/7
Sub Copy_Data()
Dim Count, Qty As Long
Dim CatRng, MonthRng, SDate, CxlDate, PoNumb, Vendor As Range
Dim Total As Currency
Dim StrTarget As String
Dim Row, PORow, Col As Integer
Set CatRng = Sheets("NEW PO").Range("G20:G43")
Set MonthRng = Sheets("POs").Range("L122:W122")
StrTarget = Sheets("New PO").Range("V12")
Set SDate = Sheets("New PO").Range("T12")
Set CxlDate = Sheets("New PO").Range("T13")
Set PoNumb = Sheets("New PO").Range("N10")
Set Vendor = Sheets("New PO").Range("D14")
Count = 0
For Count = 0 To 99
Total = 0
Qty = 0
'So that the values reset each time the cat changes
For Each cell In CatRng
'To get the row number then total the required information
If cell.Value = Count Then
Row = cell.Row
Qty = Qty + Sheets("NEW PO").Range("S" & Row).Value
Total = Total + Sheets("NEW PO").Range("Z" & Row).Value
'I guessed ext cost only as it has been totaled at the bottom,
'this is easily changed though
End If
Next cell
'Now put the totals into a PO only if there is a quantity of items
If Qty > 0 Then
PORow = Sheets("POs").Range("K1048576").End(xlUp).Row + 1
'I'll let you sort the PO number and other fields out but the main 3 are done below
With Sheets("POs")
.Range("I" & PORow).Value = Qty
.Range("K" & PORow).Value = Count
.Range("C" & PORow).Value = SDate
.Range("D" & PORow).Value = CxlDate
.Range("B" & PORow).Value = PoNumb
.Range("F" & PORow).Value = Vendor
'My understanding here is that the target month in T12 is in the same format as
'the anticipated Receipt month, I hope this is what you were looking for
For Each cell In MonthRng
If cell.Value = StrTarget Then
Col = cell.Column
.Cells(PORow, Col).Value = Total
'Used .cells here as both column and row are now integers
'(only way i can ever get it to work)
End If
Next cell
End With
End If
Next Count
End Sub
Okay, this seems to fit what you want it to do, please let me know if it doesn't.
It's a bit long as I just whacked this together between meetings but it does the job. You can use the row and column indexes to manipulate any other data you wish to pull across or amend which cost you want to be totaled up.
Sub Test()
Dim Count, Qty As Long
Dim CatRng, MonthRng As Range
Dim Total As Currency
Dim Row, PORow, Col As Integer
Set CatRng = Sheets("NEW PO").Range("G19:G43")
Set MonthRng = Sheets("POs").Range("L122:W122")
Count = 0
For Count = 0 To 99
Total = 0
Qty = 0
'So that the values reset each time the cat changes
For Each Cell In CatRng
'To get the row number then total the required information
If Cell.Value = Count Then
Row = Cell.Row
Qty = Qty + Sheets("NEW PO").Range("T" & Row).Value
Total = Total + Sheets("NEW PO").Range("AA" & Row).Value
'I guessed ext cost only as it has been totaled at the bottom,
'this is easily changed though
End If
Next Cell
'Now put the totals into a PO only if there is a quantity of items
If Qty > 0 Then
PORow = Sheets("POs").Range("J1048576").End(xlUp).Row + 1
'I'll let you sort the PO number and other fields out but the main 3 are done below
With Sheets("POs")
.Range("I" & PORow).Value = Qty
.Range("J" & PORow).Value = Count
'My understanding here is that the target month in T12 is in the same format as
'the anticipated Receipt month, I hope this is what you were looking for
For Each Cell In MonthRng
If Cell.Value = .Range("T12").Value Then
Col = Cell.Column
.Cells(PORow, Col).Value = Total
'Used .cells here as both column and row are now integers
'(only way i can ever get it to work)
End If
Next Cell
End With
End If
Next Count
End Sub
这篇关于VBA根据过滤器进行复制和粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
