Excel Vba to copy a range of data in columns to another sheet

Question

I am trying to copy a set of data from many column from a sheet of one workbook to a sheet of another workbook without opening the second workbook.

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

The sheet1 in the second workbook also is in the same column names and configuration.

what i want to achieve is what ever i enter in workbook1 copy to workbook2. from the next available blank row.

here is my code

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

when ever i click the button it gives a run time error. Run-time error'1004': Method 'Range' of object'_worksheet' failed. and the debug highlight " itemIndex = Range("A8:A & LR") ".

want a solution as soon as possible.

copy a set of columns from one workbook to another without opening the another workbook? — Karthick Gunasekaran

Vityata Vityata · Accepted Answer · 2016-04-06T08:07:26

0

votes

Try setting it like this for all the ranges:

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

Excel Vba to copy a range of data in columns to another sheet

2 Answers