I'm attempting to copy a range with variable arguments

Question

I've tried the following unsuccessfully.

Sub TestRange()

Dim example As Range

Dim RangeStart As Long

Dim RangeEnd As Long

RangeStart = ActiveSheet.Cells(1, 1)

RangeEnd = ActiveSheet.Cells(3, 4)

Dim ws As Worksheet

Set ws = Worksheets("Sheet5")

With ws

example = ws.Range("E" & .Rows.Count).End(xlUp).Row

Set example = Range(.Cells(RangeStart, 1), .Cells(RangeEnd, 8))

End With

example.Select

Selection.Copy

End Sub

Hi, welcome to stack overflow. What problem are you having? are you receiving na error of some kind? Could you add some additional details possibly? Thanks — Matt

Unknown Unknown · Accepted Answer · 2019-01-22T00:01:37

You're missing the . in .Range that defines the copy area within the With/End With block.

Dim example As Range
Dim RangeStart As Long, RangeEnd As Long

RangeStart = ActiveSheet.Cells(1, 1)
RangeEnd = ActiveSheet.Cells(3, 4)

With Worksheets("Sheet5")

    'I don't know what the following is intended to do - it has no purpose
    'example = ws.Range("E" & .Rows.Count).End(xlUp).Row

    Set example = .Range(.Cells(RangeStart, 1), .Cells(RangeEnd, 8))

End With

example.copy

I'm attempting to copy a range with variable arguments

1 Answers