vba Excel 宏将特定行的单元格从一个工作表剪切到另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17305650/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel macro to cut a specific row of cells from one worksheet to another
提问by gangnam
I have a worksheet titled CASES-PENDING, with many rows of data. On a daily basis I change the status of a particular row's beginning cell to "done" (changed from "pending"). Instead of then having to cut that row and paste on my other CASES-DONE titled worksheet, I'd like a macro to do that. I want to run the macro after changing the status of several rows of data, from "pending" to "done". Then all those rows must be cut and pasted on the other worksheet. Is that possible? Thanks so much guys!
我有一个名为 CASES-PENDING 的工作表，其中包含多行数据。我每天都会将特定行的起始单元格的状态更改为“完成”（从“待定”更改）。然后不必剪切该行并粘贴到我的另一个 CASES-DONE 标题工作表上，我想要一个宏来做到这一点。我想在将几行数据的状态从“待处理”更改为“完成”后运行宏。然后必须将所有这些行剪切并粘贴到另一个工作表上。那可能吗？非常感谢你们！
回答by BrianCD24
This is just a stab in the dark, but I've been handling something similar to this as of late.
这只是在黑暗中刺伤，但我最近一直在处理类似的事情。
Dim LastRow As Long
Range("1:1").AutoFilter Field:=(Row you have "Done" in), Criteria1:="Done"
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
Range("CellRangeYouNeedCopied" & LastRow).Copy Destination:=Sheets("SheetX").Range(X,Y)
Basically this filters to only the rows with DONE in them, and copys and pastes them on whatever other sheet you decide to name. Just remember to replace all of the variables. I'm still pretty new at this, so I may be wrong but it's worth a shot!
基本上，这仅过滤到其中包含 DONE 的行，并将它们复制并粘贴到您决定命名的任何其他工作表上。请记住替换所有变量。我在这方面还很陌生，所以我可能错了，但值得一试！
Edit:You can also just record this as a macro, and then change the range so that it's variable using the Long variable.
编辑：您也可以将其记录为宏，然后使用 Long 变量更改范围以使其可变。
回答by Olaf H
You can also do it like this. More code, than the AutoFilter solution, but maybe more flexible.
你也可以这样做。比 AutoFilter 解决方案更多的代码，但可能更灵活。
Sub MoveDoneRows()
Dim nStatusCol As Integer
nStatusCol = 1
Dim i As Integer
i = 2
' select first row to insert rows into DONE sheet
Dim nInsertRow As Integer
Sheets("CASES-DONE").Select
Range("A1").Select
Selection.End(xlDown).Select
nInsertRow = ActiveCell.Row + 1
' move rows with status done
Dim sStatus As String
Dim sPasteRow As String
sStatus = Sheets("CASES-PENDING").Cells(i, nStatusCol).Value
While sStatus <> ""
If sStatus = "done" Then
' cut the current row from PENDING sheet
sPasteRow = i & ":" & i
Sheets("CASES-PENDING").Select
Rows(sPasteRow).Select
Selection.Cut
' paste into DONE sheet
Sheets("CASES-DONE").Select
Cells(nInsertRow, nStatusCol).Select
ActiveSheet.Paste
nInsertRow = nInsertRow + 1
' delete empty row from PENDING sheet
Sheets("CASES-PENDING").Select
Rows(sPasteRow).Select
Selection.Delete Shift:=xlUp
Else
i = i + 1
End If
sStatus = Sheets("CASES-PENDING").Cells(i, nStatusCol)
Wend
End Sub
