我有一个问题，我需要从一个包含四个工作表“data.xlsx”（工作表1，2，3，4）的工作簿中复制具有4列的行到另一个名为“newdata.xlsxm”（工作表1）的工作簿中的一个工作表。
我已经成功地设置了复制路径和其他代码，但我的问题是正确复制行数。对于“data.xlsx”中的工作表1，2，3，4，行数可以从0到60（通常在0到20行左右）。
我目前的问题是，当data.xlsx中的一个或多个工作表具有零行时。如果是这样，宏运行“VBA错误1004 -应用程序定义或对象定义的错误”，例如工作表2具有零行。Excel在以下位置获得错误：

'copy 
column 1 worksheets("worksheet2").cells(p,1).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(q,1)

as q=0.我明白这是不可能的复制文本到行= 0，这是不可取的要么.然而，这是尽我所能对我自己，我会感谢任何类型的帮助.
我开始涉足一点“后藤”-语句，但很快就投降了，因为我意识到我在深水中。
我会很感激任何建议，如何走得更远！
我目前的代码如下：

sub copyfile

'read data.xlsx file

'open data file
workbooks.open filename :=thisworkbook.path & "\data.xlsx"

dim x as integer

'dim lr as long
lr= cells(rows.count,1).end(xlup).row

for x = 2 to lr

'copy rows from sheet1 on data.xlsx to new file, new file called "newdata.xlsm"

'copy column 1 
worksheets("worksheet1").cells(x,1).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(x,1)

'copy column 2
worksheets("worksheet1").cells(x,2).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(x,2)

'copy column 3
worksheets("worksheet1").cells(x,3).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(x,3)

'copy column 4
worksheets("worksheet1").cells(x,4).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(x,4)


next x

windows("data.xlsx").Activate
Sheets("Worksheet2").Activate

'dim copy worksheet 2
dim y as integer
dim z as integer
dim lr_y as long

'no of rows on worksheet2
lr_y = cells(rows.count,1).end(xlup).row
For y = 2 to lr_y

For z = (y + x) -2 to lr_y + x - 2

'copy rows from sheet2 on data.xlsx to new file, new file called "newdata.xlsm"

'copy column 1 
worksheets("worksheet2").cells(y,1).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(z,1)

'copy column 2
worksheets("worksheet2").cells(y,2).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(z,2)

'copy column 3
worksheets("worksheet2").cells(y,3).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(z,3)

'copy column 4
worksheets("worksheet2").cells(y,4).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(z,4)


next z
next y

windows("data.xlsx").Activate
Sheets("Worksheet3").Activate

'dim copy worksheet 3
dim p as integer
dim q as integer
dim lr_p as long

'no of rows on worksheet3
lr_p = cells(rows.count,1).end(xlup).row
For p = 2 to lr_p

For q = (p+z) -2 to lr_p + z - 2

'copy column 1 
worksheets("worksheet3").cells(p,1).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(q,1)

'copy column 2
worksheets("worksheet3").cells(p,2).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(q,2)

'copy column 3
worksheets("worksheet3").cells(p,3).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(q,3)

'copy column 4
worksheets("worksheet3").cells(p,4).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(q,4)


next q
next p

windows("data.xlsx").Activate
Sheets("Worksheet4").Activate

'dim copy worksheet 3
dim r as integer
dim s as integer

'no of rows on worksheet4
dim lr_p as long
lr_p = cells(rows.count,1).end(xlup).row

For r = 2 to lr_r

For s = (r+q) -2 to lr_r + q - 2

'copy column 1 
worksheets("worksheet4").cells(r,1).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(s,1)

'copy column 2
worksheets("worksheet4").cells(r,2).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(s,2)

'copy column 3
worksheets("worksheet4").cells(r,3).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(s,3)

'copy column 4
worksheets("worksheet4").cells(r,4).copy workbooks("newdata.xlsm").worksheets("sheet1").cells(s,4)

next s
next r

workbooks("data.xlsx").close

我试着改变：

For q = (p+z) -2 to lr_p + z - 2

包含x的变体，但是这并不起作用，因为我需要代码在两种情况下都起作用，如果行被填充或不填充。