asked on

Excel VBA: Code to copy data from one workbook to another

Dear all,

I'm creating a "Tool" with Excel with an external Database. So i'm working with 2 workbooks.

In the "Tool" workbook i have data on sheet (sheet5) that i want to save in the "Database" workbook. The data that i want to save is linked to other data in the database so i can't always add it on the first blank row.

Counting the rows of data that i want to add and insert that amount of empty rows in the database works, but the loop to copy the data from the "Tool" workbook to the "database" workbook doesn't. I always receive one or another error, most of the time it's "Run-Time Error 9 - Subscript out of range".

             Set Wb = Workbooks("Database.xlsm")
                        Set Ws = Wb.Worksheets("Database")
                        Dim qty As Integer
                        Dim MyRange As Range
                        Dim MyRange2 As Range
                        Dim IDU As Range
                        Dim nr As Integer                        
                        
                        Set MyRange = Sheet5.Range("E46:E109")
                        qty = Application.CountA(MyRange)
                        nr = 45 + qty
                        Set MyRange2 = Sheet5.Range("E46:E" & nr)
                        
                        Do
                        Range(c2.Offset(1, 0), c2.Offset(qty, 0)).EntireRow.Insert
                        If c2.Offset(1, 0) = "" Then Exit Do
                        Loop
                        
                        For Each IDU In MyRange2
                        Sheet5.Range(IDU).Value.Copy _
                            Destination:=Ws.Range(c2.Offset(1, 0))
                            
                        Set c2 = Cells(c2.Offset(1, 0).Row + 1, 0)
                        Next IDU

Open in new window

c2 is declared earlier in the code and is my link to the data already existing in the database where i want to save the new data under.

It's the first time that i try to create a loop so all info is welcome ;-) Thanks in advance

Kanti Prasad

Hi

Change
Dim IDU As Range

to

Dim IDU As Variant

ManuHuyghe

ASKER

Thanks for the fast reaction.

Now i'm receiving Run-time error 1004 (Methode ‘Range’ of object’_worksheet failed) on this part of the code:

Sheet5.Range(IDU).Value.Copy _
                            Destination:=Ws.Range(c2.Offset(1, 0))

Open in new window

When i use the Debugger i can see that it can read the value to copy, but my destination is wrongly set.

Martin Liss

Could you please post sample workbooks for both the source and database workbooks?

ManuHuyghe

ASKER

Attached, you find the complet tool that i'm creating.

You have to save the 3 files in the same folder.

You find the part with the problem on userform "frmUnit" in "Private Sub CommandButton4_Click()"

As you can see, i have 3 points when i must be able to write data from the 'Tool' workbook to the 'database' Workbook.
2 times with "Private Sub CommandButton4_Click()" To add new units
1 time with "Private Sub CommandButton5_Click()" To modify existing units

I started in "Private Sub CommandButton4_Click()" because it's should be the easiest one. It's in the last part of the code of "Private Sub CommandButton4_Click()"
Database.xlsm
signature.png
Daikin-s-Service-Tool-v1.0-v31.xlsm

ASKER CERTIFIED SOLUTION

ManuHuyghe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.