Excel - 宏，复制到新工作簿不适用于8行中的多个行 [英] Excel - Macro, Copy to new Workbook not working for multiple of 8 Rows

问题描述

我试图使用宏代码将特定数量的行从.xlsm格式工作簿复制到.csv文件。如果我必须复制7行代码工作正常。这里是7行被复制到.csv文件的屏幕截图。
Screenshot 1

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

当我尝试以8的倍数复制行时，会出现问题。8行无限地复制到工作表的末尾，如下所示：
截图2

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

这里是我使用的代码。 （对于7或8行，代码保持不变）

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

*这里k1初始化为Long，值为1， LastRow 值为7（或8）。注意Col1是第一个要复制的列。

* 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.

我是新的Excel宏，任何帮助将不胜感激。

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

推荐答案

Excel会尝试用您复制的范围填充整个目标范围。

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.

如果源中的行数是目标行的一个因子，则Excel会反复重复来源以填充整个目标区域。

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.

在你的情况下，7不是1048576的因素，所以只有一个副本。 8是因子1048576，因此制作了131072个副本。对于1行，2行，4行，16行，32行等的源范围，将发生相同的重复。

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.

您的代码可以重写为：

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")

这篇关于Excel - 宏，复制到新工作簿不适用于8行中的多个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！