Copy a variable range in excel with VBA macro

Question

I am a little confused. I created a button with a macro that copy a selection of cells from another sheet s in my actual sheet s0 (both s and s0 are string variables)

Sheets(s0).Range("D56:K80").Value = Sheets(s).Range("L2:S26").Value

and all works. But If I try to write something more flexible, such as a range that can vary the starting row

x = Cells(2, 4).Value 'x is a number defined by user
Sheets(s0).Range(Cells(56, 4), Cells(80, 11)).Value = Sheets(s).Range(Cells(2 + x, 2), Cells(26 + x, 9)).Value

nothing works and 1004 error appears. What is the problem? What am I doing wrong?

The situation does not change if I compute the sums out of the Cells command. But something like

Sheets(s).Cells(r, c) = x

works when I change the value of a single cell with variabiles values of r and c. (x, r and c are always integer)

Thank you in advance

user3598756 · Accepted Answer · 2017-03-16 07:29:02Z

1

you must qualify all ranges references up to worksheet object:

Sheets(s0).Range(Sheets(s0).Cells(56, 4), Sheets(s0).Cells(80, 11)).Value = Sheets(s).Range(Sheets(s).Cells(2 + x, 2), Sheets(s).Cells(26 + x, 9)).Value

the use of With-End With syntax can relieve the typing burden a little

With Sheets(s)
    Sheets(s0).Range(Sheets(s0).Cells(56, 4), Sheets(s0).Cells(80, 11)).Value = .Range(.Cells(2 + x, 2), .Cells(26 + x, 9)).Value
End With

while if you know for sure that "s0" is the active sheet (i.e. the one to which all unqualified worksheet reference default) while the macro runs then you can omit its qualification:

With Sheets(s)
    Range(Cells(56, 4), Cells(80, 11)).Value = .Range(.Cells(2 + x, 2), .Cells(26 + x, 9)).Value
End With

answered Mar 16, 2017 at 7:29

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marcoresk Over a year ago

Thank you very much for explanations! I also found alternative solution that is Sheets(s0).Range("D56:K80").Value = Sheets(s).Range("B2:I26").Offset(x, 0).Value (I found solutions by myself always "after" posting the question!) but thanks to you I understand my error.

Collectives™ on Stack Overflow

Copy a variable range in excel with VBA macro

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related