Excel VBA:基于行和列标题的复制和粘贴选择。

I am trying to create a macro that will format and create a weekly report. It has five columns that need to be moved: Key, Summary, Created, Status, Fix Version/s.

我正在尝试创建一个宏来格式化并创建一个每周报告。它有5列需要移动:Key、Summary、Created、Status、Fix Version/s。

I need to copy a selection that starts at Row 2 in the Key column and ends at the last row in the Fix Version/s column that will then be pasted into a sheet called "Priority Issues". I am unsure how to code this specific selection.

我需要复制一个选项，从键列的第2行开始，到Fix Version/s列的最后一行结束，然后粘贴到一个名为“优先事项”的表中。我不确定如何编码这个特定的选择。

I need to store the last row in a variable, as the last row may change from week to week. In essence, I am looking for code that allows me to make a selection from the intersection of row 2 and the Key column to the intersection of the last row and the Fix Version/s column, but am unsure how to do that.

我需要将最后一行存储在一个变量中，因为最后一行可能每周都在变化。本质上，我正在寻找的代码允许我从第2行和关键列的交点到最后一行和Fix Version/s列的交点进行选择，但是我不确定如何这样做。

Function FindCol(toFind As String) As Range
Dim Rtn As Range
Set Rtn = Rows(1).Find(What:=toFind, LookIn:=xlValues, _
          LookAt:=xlWhole, MatchCase:=True)
Set FindCol = Rtn
End Function

Sub Move_Severity()
Dim Severity As Range
Dim Key As Range
Dim Fix_Version As Range
Dim LastRow As Long

Set Severity = FindCol("Severity")
Set Key = FindCol("Key")
Set Fix_Version = FindCol("Fix Version/s")
LastRow = Cells(Rows.Count, Severity.Column).End(xlUp).Row

'This is where I am running into problems
Range(Cells(2, Key), Cells(LastRow, Fix_Version)).Copy
Sheets("Priority Issues").Range("A2").Paste
End Sub

1 个解决方案

#1

Sub Move_Severity()
Dim Severity As Range
Dim Key As Range
Dim Fix_Version As Range
Dim LastRow As Long

Set Severity = FindCol("Severity")
Set Key = FindCol("Key")
Set Fix_Version = FindCol("Fix Version/s")

With ActiveSheet '<~~ change active sheet reference to whatever must it be 
    .Range(.Cells(2, Key.Column), .Cells(.Rows.Count, Severity.Column).End(xlUp)).Copy Destination:=Sheets("Priority Issues").Range("A2")
End With

End Sub

should you only need to paste values then change that With-End With part to

您是否只需粘贴值，然后将带尾的部分更改为

With ActiveSheet
    With .Range(.Cells(2, Key.Column), .Cells(.Rows.Count, Severity.Column).End(xlUp))
        Sheets("Priority Issues").Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End With

which is much faster

这是更快

#1

Sub Move_Severity()
Dim Severity As Range
Dim Key As Range
Dim Fix_Version As Range
Dim LastRow As Long

Set Severity = FindCol("Severity")
Set Key = FindCol("Key")
Set Fix_Version = FindCol("Fix Version/s")

With ActiveSheet '<~~ change active sheet reference to whatever must it be 
    .Range(.Cells(2, Key.Column), .Cells(.Rows.Count, Severity.Column).End(xlUp)).Copy Destination:=Sheets("Priority Issues").Range("A2")
End With

End Sub