复制并粘贴特定列标题下的行 VBA

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

Copy and Paste row under specific column Heading VBA

问题

这是您的代码的翻译部分:

Sub CopyRow()

Dim lastRow As Long
Dim lRow As Long
Dim Data As String
Dim Exceptions As String

Data = "Data" '工作表名称
Exceptions = "Exceptions" '工作表名称
lastRow = Sheets(Data).Range("A" & Rows.Count).End(xlUp).Row

For lRow = 2 To lastRow '循环遍历所有行

If Application.CountIf(Sheets(Exceptions).Columns("A"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
 Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
 End If
If Application.CountIf(Sheets(Exceptions).Columns("B"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
 Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
End If
If Application.CountIf(Sheets(Exceptions).Columns("C"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
 Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
End If

Next lRow

End Sub

请注意,这只是您代码的翻译部分,不包括问题的回答。

英文:

Right now I have code that is copying and pasting rows where the column "A" numbers on the "Data" sheet are found on the "Exceptions" sheet in columns A,B or C.

As of now they are pasting as last rows on the sheet, however Im wondering if theres a way to modify my code so they paste under the corresponding header from the "Exceptions" sheet.

Example 1: Numbers 456, 678 rows should be copied under the "A" Description header on the "Data" sheet since 456, 678 are labeled as "Move to A" on the "Exceptions" sheet.

Example 2: Number 123 row should be copied under the "B" Description header since labeled "Move to B" on "Exceptions" sheet

Example 3: Numbers 345 should be copied under the "C" description header on "Data" sheet since labled "Move to C" on the "Exceptions" sheet.

Sub CopyRow()

Dim lastRow As Long
Dim lRow As Long
Dim Data As String
Dim Exceptions As String

Data = "Data" 'Sheet name
Exceptions = "Exceptions" 'Sheet name
lastRow = Sheets(Data).Range("A" & Rows.Count).End(xlUp).Row

   For lRow = 2 To lastRow 'Loop through all rows

If Application.CountIf(Sheets(Exceptions).Columns("A"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
 Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
 End If
If Application.CountIf(Sheets(Exceptions).Columns("B"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
 Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
End If
If Application.CountIf(Sheets(Exceptions).Columns("C"), Sheets(Data).Cells(lRow, "A").Value2) > 0 Then
 Sheets(Data).Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Sheets(Data).Rows(lRow).Value2
End If

Next lRow

End Sub

Data Sheet (Before)

复制并粘贴特定列标题下的行 VBA

Exceptions Sheet

复制并粘贴特定列标题下的行 VBA

Data Sheet (After)

[复制并粘贴特定列标题下的行 VBA

答案1

得分: 3

这是一段用VB语言编写的VBA宏代码,用于在Excel工作簿中移动数据行。代码中包括了从名为"Data"的工作表复制数据到名为"Exceptions"的工作表的逻辑。需要注意的是,代码中包含了一些注释和消息框,用于提示操作状态。

如果需要进一步的解释或帮助,请提出具体问题。

英文:

Feels a bit clunky, but try this out:

Sub MoveRows()

    Dim wsD As Worksheet, wsE As Worksheet, r As Long
    Dim rngData As Range, rngEx As Range, mH, mR, col As Long, hdr, v
    Dim rngCopy As Range, lr As Long, addr
    
    Set wsD = ThisWorkbook.Worksheets("Data")
    Set wsE = ThisWorkbook.Worksheets("Exceptions")
    
    Set rngData = wsD.Range("A2:B" & wsD.Cells(Rows.Count, "A").End(xlUp).Row)
    
    For col = 1 To 3  'loop the columns on the exceptions sheet
        lr = wsE.Cells(Rows.Count, col).End(xlUp).Row
        If lr > 1 Then
            'get the section header and find on "Data"
            hdr = Replace(wsE.Cells(1, col).Value, "Move to ", "")
            mH = Application.Match(hdr, rngData.Columns(2), 0)
            
            If Not IsError(mH) Then 'matched the header?
                For r = 2 To lr
                    v = wsE.Cells(r, col).Value
                    mR = Application.Match(v, rngData.Columns(1), 0)
                    If Not IsError(mR) Then
                        Set rngCopy = rngData.Rows(mR)
                        rngData.Rows(mH + 1).Insert       'add row below header
                        rngCopy.Copy rngData.Rows(mH + 1) 'copy values
                        rngCopy.Delete shift:=xlShiftUp   'delete source row
                    Else
                        'value was not matched
                        MsgBox "Value '" & v & "' not found on Data sheet!"
                    End If
                Next r
            Else
                'header not matched
                MsgBox "Header '" & hdr & "' not found on data sheet!"
            End If
        End If
    Next col
End Sub

huangapple
  • 本文由 发表于 2023年6月8日 00:22:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/76425349.html
匿名

发表评论

匿名网友

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

确定