Excel VBA 复制具有可变行号和列号的单元格 block

Question

我正在尝试选择一个单元格 block ，并将行号和列号作为变量。让我们从一个静态的开始:-

我已经得到合并单元格的行数。现在我正在尝试将用例的完整 block 复制到新工作表(与用例同名)。因此，例如，我正在尝试将用例 Random 1 的 Range("C7:K11") 复制到工作表 Random1。

我在这里遇到的唯一问题是复制带有可变行号和列号的范围。但是，此静态代码以及带有动态变量的代码不起作用:-

shtPricing.Range(Cells(7, 3), Cells(9, 11)).Copy
xWb.Worksheets(UsecaseTrail).Range(Cells(2, 3), Cells(4, 11)).PasteSpecial xlPasteValues

准确代码:

Dim lColumn As Long
Dim RowCount As Long
Dim ColumnCounter As Long
Dim RowCounter As Long
Dim tempUseCase As String

lColumn = xWb.Worksheets("Pricing").Cells(6, Columns.Count).End(xlToLeft).Column

For RowCounter = 7 To 25 
    RowCount = xWb.Worksheets("Pricing").Range("B" & RowCounter).MergeArea.Rows.Count
    If RowCount > 1 Then
        If InStr(1, CStr(xWb.Worksheets("Pricing").Range("B" & RowCounter).Value), UsecaseTrail) Then 
                xWb.Worksheets("Pricing").Range(Cells(RowCounter, 3), Cells(RowCounter + RowCount - 1, lColumn)).Copy
                xWb.Worksheets(UsecaseTrail).Range(Cells(2, 3), Cells(2 + RowCount - 1, lColumn)).PasteSpecial xlPasteValues    
            End If
        RowCounter = RowCounter + RowCount - 1 'note -1 here
    End If
Next RowCounter

请注意，一切正常。除了具有变量 Rowcounter 和 columncounter 值的复制粘贴功能。仅通过以下代码将非常有帮助:-

xWb.Worksheets("Pricing").Range(Cells(RowCounter, 3), Cells(RowCounter + RowCount - 1, lColumn)).Copy
xWb.Worksheets(UsecaseTrail).Range(Cells(2, 3), Cells(2 + RowCount - 1, lColumn)).PasteSpecial xlPasteValues

错误:

Answer 1

考虑:

Dim r1 As Range, r2 As Range
With shtPricing
    Set r1 = Range(.Cells(7, 3), .Cells(9, 11))
End With

With xWb.Worksheets(UsecaseTrail)
    Set r2 = Range(.Cells(2, 3), .Cells(4, 11))
End With

r1.Copy
r2.PasteSpecial xlPasteValues

编辑#1:

这是一个经过测试的例子:

Sub ytrewq()
    Dim shtPricing As Worksheet
    Dim r1 As Range, r2 As Range
    Dim UsecaseTrail As String
    Dim xWb As Workbook

    Set shtPricing = Sheets("Sheet1")
    Set xWb = ThisWorkbook
    UsecaseTrail = "Sheet2"

    With shtPricing
        Set r1 = Range(.Cells(7, 3), .Cells(9, 11))
    End With

    With xWb.Worksheets(UsecaseTrail)
        Set r2 = Range(.Cells(2, 3), .Cells(4, 11))
    End With

    r1.Copy
    r2.PasteSpecial xlPasteValues

End Sub

请注意 Cells()

上的 点