Ok, but nevermind I found what the problem was with my syntax, I forgot to
place the "_" to connect the statements, so apart of the code should look
like this:

Range(rngFound, rngFound.Offset(1, 8)).Copy _
Destination:=Workbooks("CI-Adagio-History-Web.xls").Worksheets
_("Sheet1").Range("A65536").End(xlUp).Offset(1, 8)

instead of this:

Range(rngFound, rngFound.Offset(1, 8)).Copy
Destination:=Workbooks("CI-Adagio-History-Web.xls").Worksheets("Sheet1").Range("A65536").End (xlUp).Offset(1, 8)

but I have one more problem, basically when it copies from the main workbook
to the other one, it pastes the values to the wrong part of the sheet, it
should be pasting it between cells A and H but instead its pasting it between
I and P, got any pointers?

"Dave Peterson" wrote:

First, I don't speak DAO.

But this line:
Set rng1 = .Range("A1:A" & .Range("A65536").End(xlDown).Row)
should probably be:
Set rng1 = .Range("A1:A" & .Range("A65536").End(xlup).Row)

I'd use:
Set rng1 = .Range("A1:A" & .cells(.rows.count,"A").End(xlup).Row)



drinese18 wrote:

I am trying to write a macro that will download a range of data from one
workbook to the next, but I am having some problems with my code, I am trying
to incorporate DAO within the code to be able to connect to the other
workbooks, but unfortunately it is not working. My DAO syntax kind of sucks
so bear with me, I hope someone can shed some light on this, some help will
be greatly appreciated:

Option Explicit

Private Sub CommandButton1_Click()
Dim rng1 As Range
Dim rngFound As Range
Dim rngCollector As String

'Dim Dest As DAO.Connection
'Dim Destination As DAO.Recordset

With Worksheets("Index")
Set rng1 = .Range("A1:A" & .Range("A65536").End(xlDown).Row)
End With
Set rngFound = rng1.Find(what:=DateValue(Me.TextBox1.Value))
Range(rngFound, rngFound.Offset(1, 8)).Copy
Destination =
Workbooks("CI-Adagio-History-Web.xls").Worksheets("Sheet1").Range("A65536").End (xlUp).Offset(1, 8)
Set rngFound = Nothing
Unload Me
End
End Sub

--

Dave Peterson