Run time error 1004 in Range(Cells())

Question

I have the following code in a number of macros, but it takes in a lot of information, which I don't need. The formula is as follows:

Range("A1" & h & ":BD" & h).Value = Worksheets("ABC1").Range("A" & h & ":BD" & h).Value

I have tried to change the formula to a more dynamic version, as follows:

Sub MFGI_NAV()

Dim x As Variant
Dim y As Variant

    Worksheets("ABC1").Activate

    h = Cells(Rows.Count, "A").End(xlUp).Row

    Set WS = Sheets.Add(before:=Worksheets("ZYX"), Type:=xlWorksheet)
    ActiveSheet.Name = "JKL"

    x = 1
    y = 1
    While y <= 82

        Cells(1, x).Select
        Range(Cells(1, x), Cells(h, x)).Value = Worksheets("ABC1").Range(Cells(1, y), Cells(h, y)).Value

        x = x + 1
        y = y + 3

    Wend
    End Sub

This formula only takes in every 3rd column in the sheet "ABC1" and put it into every incremental column in "JKL".

However, when I try this, I get a run time error 1004.

Is there any suggestions on how I can get this to work more dynamically?

BTW, the reason I ask is that while this file only has 82 columns, and 20k rows, future sheets could have up to 10k columns and 500k rows (and yes, I know that is massive, but that is the way the system generates the information, and only 1/3 of it is needed)

Answer 1

Range(Cells(1, x), Cells(h, x)).Value = Worksheets("ABC1").Range(Cells(1, y), Cells(h, y)).Value

You are getting that error because your cells objects are not fully qualified.

Try this

WS.Range(WS.Cells(1, x), WS.Cells(h, x)).Value = _
Worksheets("ABC1").Range(Worksheets("ABC1").Cells(1, y), Worksheets("ABC1").Cells(h, y)).Value

Similarly please qualify all your objects in your code.