Excel Vba用于将列中的数据范围复制到另一个工作表

Question

我正在尝试将多列中的一组数据从一个工作簿的工作表复制到另一个工作簿的工作表，而不打开第二个工作簿。

workbook 1
sheet1

Column A      Column B      Column C        Column D     Column E     etc...
index         item No       Details         Price        Cust_nam
002           23101         book            15           ahmed
003           23102         CD              5            ahmed
004           23103         DVD             6            ahmed

第二个工作簿中的sheet1也在相同的列名和配置中。

我想要实现的是我在workbook1 copy to workbook2中输入的内容。从下一个可用的空行开始。

以下是我的代码

Private Sub CommandButton1_Click()
    LR = Range("A399").End(xlUp).Row
    LR1 = Range("B399").End(xlUp).Row
    LR2 = Range("C399").End(xlUp).Row
    LR3 = Range("D399").End(xlUp).Row
    LR4 = Range("E399").End(xlUp).Row
    LR5 = Range("F399").End(xlUp).Row
    LR6 = Range("G399").End(xlUp).Row
    LR7 = Range("H399").End(xlUp).Row

    Dim itemIndex As Range
    Dim itemNumber As Range
    Dim itemDetails As Range
    Dim itemPrice As Range
    Dim itemCust_nam As Range
    Dim itemMobile As Range
    Dim itemDate As Range
    Dim itemTime As Range
    Dim myData As Workbook

Worksheets("Sheet1").Select
    itemIndex = Range("A8:A & LR")
    itemNumber = Range("B8:B" & LR1)
    itemDetails = Range("C8:C" & LR2)
    itemPrice = Range("D8:D" & LR3)
    itemCust_nam = Range("E8:E" & LR4)
    itemMobile = Range("F8:F" & LR5)
    itemDate = Range("G8:G" & LR6)
    itemTime = Range("H8:H" & LR7)


Set myData = Workbook

myData.SaveAs Filename:="C:\Users\athif\Desktop\Test_Pos\DataBase.xlsx", FileFormat:=51
    Worksheets("Sales").Select
    Worksheets("Sales").Range("A2").Select
    RowCount = Worksheets("Sales").Range("A2").CurrentRegion.Rows.Count
    With Worksheets("Sales").Range("A2")
    .Offset(RowCount, 0) = itemIndex
    .Offset(RowCount, 1) = itemNumber
    .Offset(RowCount, 2) = itemDetails
    .Offset(RowCount, 3) = itemPrice
    .Offset(RowCount, 4) = itemCust_nam
    .Offset(RowCount, 5) = itemMobile
    .Offset(RowCount, 6) = ItemData
    .Offset(RowCount, 7) = itemTime
    End With

    myData.Save
    End Sub

每当我单击该按钮时，它都会给出一个运行时错误。运行时错误‘1004’：对象‘_worksheet’的方法'Range‘失败。并且debug突出显示“itemIndex = Range("A8:A & LR")”。

希望尽快得到解决方案。

Answer 1

尝试对所有范围进行如下设置：

set itemIndex = Range("A8:A & LR")

Answer 2

假设LR是一个数字，而不是

itemIndex = Range("A8:A & LR")

它应该是

itemIndex = Range("A8:A" & LR)