Copy function with Range(Cells(a,b), Cells(c,d)) not working

Question

I have a code below which supposedly copies the values in a range of cells and pastes them at another location, but it is not working for some reason.

 For sheetnumber = 2 To ThisWorkbook.Sheets.Count
    Set currentsheet = ThisWorkbook.Sheets(sheetnumber)
    currentsheet.Range(Cells(11, 4), Cells(25, 4)).Copy
    currentsheet.Paste Destination:=currentsheet.Cells(11, 3)
 Next sheetnumber

However, when i use currentsheet.Range("D11:D25").Copy, it works and is able to copy the selected range of cells. So may I know why cant currentsheet.Range(Cells(11, 4), Cells(25, 4)).Copy work? Or is there another alternative such that I can write currentsheet.Range("D11:D25").Copy such that my column (D in this case) can be expressed in terms of a variable? Thank you.

`Cells(11, 4)` is equivalent to `ActiveSheet.Cells(11,4)`, and in your case currentsheet is not always active — Kirill Tkachenko, Apr 06 '20 at 08:58

score 2 · Accepted Answer · answered Apr 06 '20 at 08:42

2

You need to have a sheet qualifier (object) for your Cells, like below:

For sheetnumber = 2 To ThisWorkbook.Sheets.Count
    Set currentsheet = ThisWorkbook.Sheets(sheetnumber)
    currentsheet.Range(currentsheet.Cells(11, 4), currentsheet.Cells(25, 4)).Copy
    currentsheet.Paste Destination:=currentsheet.Cells(11, 3)
 Next sheetnumber

answered Apr 06 '20 at 08:42

Dean

2,326
3
13
32

a `With currentsheet … End With` block would fit in this case – HTH Apr 06 '20 at 08:44
great, thanks alot! @Dean – Ho Jun Hong Apr 06 '20 at 08:45

score 1 · Answer 2 · answered Apr 06 '20 at 08:53

Dean gave you the solution

here's some other hints to enhance your code

you could avoid to Copy and Paste in two statements by using the Destination parameter of the Copy() method of Range class
use With … End With blocks to ensure proper range qualifications and avoid repeating the same variable name

here's the code

Dim sheetnumber As Long
With ThisWorkbook ' reference 'ThisWorkbook' workbook
    For sheetnumber = 2 To .Sheets.Count ' loop from 2 to referenced workbook sheets number
        With .Sheets(sheetnumber) ' reference referenced workbook 'sheetnumber'th sheet
            .Range(.Cells(11, 4), .Cells(25, 4)).Copy Destination:=.Cells(11, 3) ' copy and paste in one shot . see all ranges are linke to the referenced sheet via the preceeding dot
        End With
    Next
End With

Copy function with Range(Cells(a,b), Cells(c,d)) not working

2 Answers2