英文:
Copy paste and delete from one sheet into another
问题
Sub CopyToReturn()
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("Return Source").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("DLS-Route").Select
Selection.EntireRow.Delete
End Sub
英文:
I'm currently trying to work some VBA code into my excel file that makes it so when I click on a Row I can click on a button to copy that row onto a different sheet and then the row gets deleted. I'm able to get all of that to work except I was wondering if there was a way to make it so I can copy & delete multiple rows? right now I can only do one at a time.
The Code is listed below, not sure if its possible to enhance it or change it so lets say I select a random number of rows and click on the copy button it can all paste at the same time.
Sub CopyToReturn()
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("Return Source").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("DLS-Route").Select
Selection.EntireRow.Delete
End Sub
答案1
得分: 1
以下是代码的翻译部分:
Option Explicit
Sub CopyToReturn()
'声明部分。
Dim RngSource As Range
Dim RngDestination As Range
Dim RngArea As Range
Dim RngCell As Range
Dim RngChop As Range
Dim DblBottomRow As Double
Dim DblTopRow As Double
Dim DblRow As Double
'设置部分。
Set RngSource = Selection
Set RngDestination = Sheets("Return Source").Range("A1").End(xlDown)
'检查RngDestination是否为空,这意味着它不是列表的最后一个单元格。
If Excel.WorksheetFunction.CountBlank(RngDestination) = RngDestination.Cells.Count Then
'设置RngDestination。
Set RngDestination = RngDestination.End(xlUp).Offset(1, 0)
Else
'设置RngDestination。
Set RngDestination = RngDestination.Offset(1, 0)
End If
'处理RngSource的每个区域。
For Each RngArea In RngSource.Areas
'处理RngArea中的每个单元格。
For Each RngCell In RngArea.Cells
'检查RngChop是否为Nothing。
If RngChop Is Nothing Then
'设置RngChop。
Set RngChop = RngCell.EntireRow
Else
'设置RngChop。
Set RngChop = Union(RngChop, RngCell.EntireRow)
End If
Next
Next
'处理RngChop的每一行。
For Each RngArea In RngChop.Rows
'复制行。
RngArea.Copy RngDestination
'设置下一行的RngDestination。
Set RngDestination = RngDestination.Offset(1, 0)
Next
'删除RngChop。
RngChop.Delete
End Sub
请注意,这是Visual Basic for Applications (VBA)的代码,用于在Excel中执行某些操作。
英文:
Try this:
Option Explicit
Sub CopyToReturn()
'Declarations.
Dim RngSource As Range
Dim RngDestination As Range
Dim RngArea As Range
Dim RngCell As Range
Dim RngChop As Range
Dim DblBottomRow As Double
Dim DblTopRow As Double
Dim DblRow As Double
'Settings.
Set RngSource = Selection
Set RngDestination = Sheets("Return Source").Range("A1").End(xlDown)
'Checking if RngDestination is empty, which means its not the last cell of the list.
If Excel.WorksheetFunction.CountBlank(RngDestination) = RngDestination.Cells.Count Then
'Setting RngDestination.
Set RngDestination = RngDestination.End(xlUp).Offset(1, 0)
Else
'Setting RngDestination.
Set RngDestination = RngDestination.Offset(1, 0)
End If
'Covering each area of RngSource.
For Each RngArea In RngSource.Areas
'Covering each cell of RngArea.
For Each RngCell In RngArea.Cells
'Checking if RngChop is nothing.
If RngChop Is Nothing Then
'Setting RngChop.
Set RngChop = RngCell.EntireRow
Else
'Setting RngChop.
Set RngChop = Union(RngChop, RngCell.EntireRow)
End If
Next
Next
'Covering each row of RngChop.
For Each RngArea In RngChop.Rows
'Copying the row.
RngArea.Copy RngDestination
'Setting RngDestination for the next row.
Set RngDestination = RngDestination.Offset(1, 0)
Next
'Deleting RngChop.
RngChop.Delete
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论