复制粘贴并从一个工作表删除到另一个工作表。

huangapple go评论57阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年5月21日 04:24:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/76297214.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定