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)
                        Range(c2.Offset(1, 0), c2.Offset(qty, 0)).EntireRow.Insert
                        If c2.Offset(1, 0) = "" Then Exit Do
                        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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kanti PrasadCommented:

Dim IDU As Range


Dim IDU As Variant
ManuHuygheAuthor Commented:
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 LissOlder than dirtCommented:
Could you please post sample workbooks for both the source and database workbooks?
Put Your Flow Data to Work

SolarWinds® Flow Tool Bundle combines three easy-to-download, easy-to-use flow analysis tools that can help you quickly distribute, test, and configure your flow traffic.

ManuHuygheAuthor Commented:
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()"
ManuHuygheAuthor Commented:
The problem is solved.

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

Open in new window

i use
c2.Offset(i, 2).Value = IDU.Value

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.