Excel - Macro, Copy to new Workbook not working fo

Question

I was trying to copy a particular number of rows from .xlsm format workbook to a .csv file using macro code. The code works fine if I have to copy 7 rows. Here's the screenshot of 7 rows being copied into the .csv file. Screenshot 1

The problem arises when I try to copy rows in the multiples of 8. The 8 rows are copied infinitely till the end of the worksheet like so: Screenshot 2

Here is the code I'm using. (The code remains the same for 7 or 8 rows)

    For I = Col1 To Last_col - 1                
            ws.Range(Cells(1, I), Cells(LastRow, I)).Copy Destination:=wsNew.Columns(k1)
            k1 = k1 + 1
       Next I

* Here k1 is initialised as Long with value 1 and the LastRow variable takes on the value of 7 (or 8). Note that Col1 is the first column to be copied.

I am new to Excel macros and any help would be appreciated.

Answer 1

Excel attempts to fill the entire destination range with your copied range.

Where the number of rows in the source is not a factor of the destination rows, it determines that it isn't possible to do so evenly and just copies the source once.

Where the number of rows in the source is a factor of the destination rows, Excel just repeats the source over and over to fill the entire destination area.

In your case, 7 is not a factor of 1048576 so only one copy is made. 8 is a factor of 1048576 so 131072 copies are made. The same "duplication" will occur with a source range of 1 row, 2 rows, 4 rows, 16 rows, 32 rows, etc.

Your code could be rewritten as:

For I = Col1 To Last_col - 1                
    ws.Range(ws.Cells(1, I), ws.Cells(LastRow, I)).Copy Destination:=wsNew.Range(wsNew.Cells(1, k1), wsNew.Cells(LastRow, k1))
    k1 = k1 + 1
Next I

Or you could avoid the loop and just use:

ws.Range(ws.Cells(1, Col1), ws.Cells(LastRow, Last_col - 1)).Copy wsNew.Range("A1")