Excel VBA Copy block of cells with variable row and column number

Question

I am trying to select a block of cells with having the row and column number as variable. Lets start with a static one:-

I already got the row count for the merged cells. Now I am trying to copy the complete block for a Use case to a new sheet (with same name as Use case). So for example I am trying to Copy Range("C7:K11") of use case Random 1 to Worksheet Random1.

The only issue, I am encountering here is to copy this Range with varibale row number and column number. However, This static code as well as code with dynamic variable is not working:-

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

Exact code:

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

Please note that everything is working fine. Except for the copy paste function with variable Rowcounter and columncounter value. It would be really helpful to get through following code only :-

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

Error :

Gary's Student Gary's Student · Accepted Answer · 2017-11-03T16:20:55

Consider:

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

EDIT#1:

Here is a tested example:

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

Please note the dots on the Cells()

Excel VBA Copy block of cells with variable row and column number

1 Answers