Unasked Question is the Dumb Question

[BManke]

Hi!

the "\\" is just there so you see the comment more easily. No functionality other than that. you can leave them out if you dont like them.

I'd like to continue by trying to simply copy the data over from one datarange to the other. Next step will be to filter and change the data in the process of copying. But first: just copy, to make sure the basic routine runs correctly.

First I must redirect you to naming the ranges. I made a mistake there with the targetrange's size: The targetrange should be only including the headers. I wrote: "same size" that's wrong. Just the headerline.

Next you need to add a reference.
This can be done in one of the menus under "References..." - (in my German Excel version it's in the menu "Extras" it might be called "Tools" or something in other language version...just look for "references..." in all menus, should be the first item"

The reference we need is down the list called: 'Microsoft ActivX Data Objects 2.8 Library'
If you dont have 2.8, then use 2.5 (I believe 2.6 and 2.7 had some issues)

Now back to the coding

In your module add a new sub called TransferData and add the code below.
We will be re-using this code for actually solving your problem in the next step.

Option Explicit

Sub TransferData()
'\\Purpose: Copy data from one datarange to another
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim lngRecordsAffected As Long
Dim strConn As String
Dim strSourceFile As String
Dim strSourceRange As String
Dim strTargetRange As String
Dim strQry As String

strSourceFile = "C:\MyExcel1.xls"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSourceFile & ";Extended Properties=""Excel 8.0;HDR=YES"";"
'check if it got assembled correctly
Debug.Print strConn

'open a connection to that file
Set cnn = New ADODB.Connection
cnn.ConnectionString = strConn
cnn.Open

'prepare the SQL command for data manipulation -- this will be the only part that needs changed to add actual functionality
strSourceRange = "SourceRange"
strTargetRange = "TargetRange"
strQry = "INSERT INTO " & strTargetRange & _
         " SELECT * FROM " & strSourceRange
'check if it got assembled correctly
Debug.Print strQry

'prepare execution of the SQL command with a command object
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = strQry
cmd.Execute lngRecordsAffected
MsgBox lngRecordsAffected & " records copied"

'clean up (only causes error when everything is clean already, so we tell Excel to ignore errors)
On Error Resume Next
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
On Error GoTo 0
End Sub

This is one approach of doing it. It has the advantage of being efficient for certain datamanipulations. It has the disadvantage that it introduces SQL, which you might not be familiar with. I'll show you a different approach later, that you can use for fine-tuning your data later yourself.