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

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

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
>

This portion of the .copy line:

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

Says to start at the bottom of column A, go up to the last used cell, come down
one row and then over 8 columns.

So I'd change that .offset(1,8) to .offset(1,0)
to stay in the same column.

drinese18 wrote:
>
> 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
> >

--

Dave Peterson

Yeh I did that already but regardless I abandoned that code and wrote a
different one, it works but I want it to take the last data entered on the
main worksheet then copy it to the 2 other workbooks, reason for this is that
the main workbook gets updated everyday, so I don't want it from just a set
range but rather the last part of the workbook that is updated, my code can
be seen below:


Sub Copydata()


Range("A3111:H3111").Select
Selection.Copy

'Path to file to copy from
Workbooks.Open Filename:= _
"C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
Updated\CI-Adagio-History-Web.xls"

'Appends data
ActiveSheet.Cells(rows.count, "A").End(xlUp).Offset(0, 1).Select

'Paste Special
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A3114").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Range("A3111").Select

'Scroll to empty cell
ActiveWindow.ScrollRow = 3054
ActiveWindow.ScrollRow = 2572
ActiveWindow.ScrollRow = 1929
ActiveWindow.ScrollRow = 1528
ActiveWindow.ScrollRow = 1126
ActiveWindow.ScrollRow = 805
ActiveWindow.ScrollRow = 563
ActiveWindow.ScrollRow = 403
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5

'Copies from current date
Range("K1").Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
Updated\Adagio-daily.xls" _
, UpdateLinks:=0

'Paste special values, date
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

End Sub


Hope you can help me with this, it would be really appreciated, thanks

"Dave Peterson" wrote:

> This portion of the .copy line:
>
> Destination:=Workbooks("CI-Adagio-History-Web.xls") _
> .Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 8)
>
> Says to start at the bottom of column A, go up to the last used cell, come down
> one row and then over 8 columns.
>
> So I'd change that .offset(1,8) to .offset(1,0)
> to stay in the same column.
>
> drinese18 wrote:
> >
> > 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
> > >
>
> --
>
> Dave Peterson
>

Untested, but it did compile:

Option Explicit
Sub Copydata()

Dim RngToCopy As Range
Dim ActSheet As Worksheet
Dim wkbk As Workbook
Dim DestCell As Range

Set ActSheet = ActiveSheet
With ActSheet
Set RngToCopy = .Range("A3111:H3111")
End With

'Path to file to paste
Set wkbk = Workbooks.Open _
(Filename:="C:\Documents and Settings\jermaine_wanyou\" _
& "Desktop\Test_Adagio Updated\CI-Adagio-History-Web.xls")

With wkbk.Worksheets(1) 'or wkbk.worksheets("somesheetnamehere")
'next open cell in column A--I changed the .offset
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wkbk.Close savechanges:=True

With ActSheet
Set RngToCopy = .Range("K1")
End With

Set wkbk = Workbooks.Open _
(Filename:="C:\Documents and Settings\jermaine_wanyou\" _
& "Desktop\Test_Adagio Updated\Adagio-daily.xls", _
UpdateLinks:=0)

With wkbk.Worksheets(1) 'or wkbk.worksheets("somesheetnamehere")
Set DestCell = .Range("a2")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

wkbk.Close savechanges:=True

Application.CutCopyMode = False

End Sub



drinese18 wrote:
>
> Yeh I did that already but regardless I abandoned that code and wrote a
> different one, it works but I want it to take the last data entered on the
> main worksheet then copy it to the 2 other workbooks, reason for this is that
> the main workbook gets updated everyday, so I don't want it from just a set
> range but rather the last part of the workbook that is updated, my code can
> be seen below:
>
> Sub Copydata()
>
>
> Range("A3111:H3111").Select
> Selection.Copy
>
> 'Path to file to copy from
> Workbooks.Open Filename:= _
> "C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
> Updated\CI-Adagio-History-Web.xls"
>
> 'Appends data
> ActiveSheet.Cells(rows.count, "A").End(xlUp).Offset(0, 1).Select
>
> 'Paste Special
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("A3114").Select
> Application.CutCopyMode = False
> ActiveWorkbook.Save
> ActiveWindow.Close
> Range("A3111").Select
>
> 'Scroll to empty cell
> ActiveWindow.ScrollRow = 3054
> ActiveWindow.ScrollRow = 2572
> ActiveWindow.ScrollRow = 1929
> ActiveWindow.ScrollRow = 1528
> ActiveWindow.ScrollRow = 1126
> ActiveWindow.ScrollRow = 805
> ActiveWindow.ScrollRow = 563
> ActiveWindow.ScrollRow = 403
> ActiveWindow.ScrollRow = 242
> ActiveWindow.ScrollRow = 81
> ActiveWindow.ScrollRow = 1
> ActiveWindow.ScrollColumn = 2
> ActiveWindow.ScrollColumn = 3
> ActiveWindow.ScrollColumn = 4
> ActiveWindow.ScrollColumn = 5
>
> 'Copies from current date
> Range("K1").Select
> Selection.Copy
> Workbooks.Open Filename:= _
> "C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
> Updated\Adagio-daily.xls" _
> , UpdateLinks:=0
>
> 'Paste special values, date
> Range("A2").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> ActiveWorkbook.Save
> ActiveWindow.Close
>
> End Sub
>
> Hope you can help me with this, it would be really appreciated, thanks
>
> "Dave Peterson" wrote:
>
> > This portion of the .copy line:
> >
> > Destination:=Workbooks("CI-Adagio-History-Web.xls") _
> > .Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 8)
> >
> > Says to start at the bottom of column A, go up to the last used cell, come down
> > one row and then over 8 columns.
> >
> > So I'd change that .offset(1,8) to .offset(1,0)
> > to stay in the same column.
> >
> > drinese18 wrote:
> > >
> > > 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
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

ok it works just the same as my code but the thing is I want it to copy the
last updated range, not necessarily A3111 to H3111, the workbook gets updated
everyday, so whatever was entered last I it to copy it, not just from A3111
to H3111 only, is there anyway to do that?

"Dave Peterson" wrote:

> Untested, but it did compile:
>
> Option Explicit
> Sub Copydata()
>
> Dim RngToCopy As Range
> Dim ActSheet As Worksheet
> Dim wkbk As Workbook
> Dim DestCell As Range
>
> Set ActSheet = ActiveSheet
> With ActSheet
> Set RngToCopy = .Range("A3111:H3111")
> End With
>
> 'Path to file to paste
> Set wkbk = Workbooks.Open _
> (Filename:="C:\Documents and Settings\jermaine_wanyou\" _
> & "Desktop\Test_Adagio Updated\CI-Adagio-History-Web.xls")
>
> With wkbk.Worksheets(1) 'or wkbk.worksheets("somesheetnamehere")
> 'next open cell in column A--I changed the .offset
> Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
> End With
>
> RngToCopy.Copy
> DestCell.PasteSpecial Paste:=xlPasteValues, _
> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
>
> wkbk.Close savechanges:=True
>
> With ActSheet
> Set RngToCopy = .Range("K1")
> End With
>
> Set wkbk = Workbooks.Open _
> (Filename:="C:\Documents and Settings\jermaine_wanyou\" _
> & "Desktop\Test_Adagio Updated\Adagio-daily.xls", _
> UpdateLinks:=0)
>
> With wkbk.Worksheets(1) 'or wkbk.worksheets("somesheetnamehere")
> Set DestCell = .Range("a2")
> End With
>
> RngToCopy.Copy
> DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
>
> wkbk.Close savechanges:=True
>
> Application.CutCopyMode = False
>
> End Sub
>
>
>
> drinese18 wrote:
> >
> > Yeh I did that already but regardless I abandoned that code and wrote a
> > different one, it works but I want it to take the last data entered on the
> > main worksheet then copy it to the 2 other workbooks, reason for this is that
> > the main workbook gets updated everyday, so I don't want it from just a set
> > range but rather the last part of the workbook that is updated, my code can
> > be seen below:
> >
> > Sub Copydata()
> >
> >
> > Range("A3111:H3111").Select
> > Selection.Copy
> >
> > 'Path to file to copy from
> > Workbooks.Open Filename:= _
> > "C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
> > Updated\CI-Adagio-History-Web.xls"
> >
> > 'Appends data
> > ActiveSheet.Cells(rows.count, "A").End(xlUp).Offset(0, 1).Select
> >
> > 'Paste Special
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Range("A3114").Select
> > Application.CutCopyMode = False
> > ActiveWorkbook.Save
> > ActiveWindow.Close
> > Range("A3111").Select
> >
> > 'Scroll to empty cell
> > ActiveWindow.ScrollRow = 3054
> > ActiveWindow.ScrollRow = 2572
> > ActiveWindow.ScrollRow = 1929
> > ActiveWindow.ScrollRow = 1528
> > ActiveWindow.ScrollRow = 1126
> > ActiveWindow.ScrollRow = 805
> > ActiveWindow.ScrollRow = 563
> > ActiveWindow.ScrollRow = 403
> > ActiveWindow.ScrollRow = 242
> > ActiveWindow.ScrollRow = 81
> > ActiveWindow.ScrollRow = 1
> > ActiveWindow.ScrollColumn = 2
> > ActiveWindow.ScrollColumn = 3
> > ActiveWindow.ScrollColumn = 4
> > ActiveWindow.ScrollColumn = 5
> >
> > 'Copies from current date
> > Range("K1").Select
> > Selection.Copy
> > Workbooks.Open Filename:= _
> > "C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
> > Updated\Adagio-daily.xls" _
> > , UpdateLinks:=0
> >
> > 'Paste special values, date
> > Range("A2").Select
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Application.CutCopyMode = False
> > ActiveWorkbook.Save
> > ActiveWindow.Close
> >
> > End Sub
> >
> > Hope you can help me with this, it would be really appreciated, thanks
> >
> > "Dave Peterson" wrote:
> >
> > > This portion of the .copy line:
> > >
> > > Destination:=Workbooks("CI-Adagio-History-Web.xls") _
> > > .Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 8)
> > >
> > > Says to start at the bottom of column A, go up to the last used cell, come down
> > > one row and then over 8 columns.
> > >
> > > So I'd change that .offset(1,8) to .offset(1,0)
> > > to stay in the same column.
> > >
> > > drinese18 wrote:
> > > >
> > > > 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
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Maybe...

If you only make one entry (last used row), you could use that.

If you make multiple entries, maybe you could add an indicator column that says
this should be copied or left alone.

drinese18 wrote:
>
> ok it works just the same as my code but the thing is I want it to copy the
> last updated range, not necessarily A3111 to H3111, the workbook gets updated
> everyday, so whatever was entered last I it to copy it, not just from A3111
> to H3111 only, is there anyway to do that?
>
> "Dave Peterson" wrote:
>
> > Untested, but it did compile:
> >
> > Option Explicit
> > Sub Copydata()
> >
> > Dim RngToCopy As Range
> > Dim ActSheet As Worksheet
> > Dim wkbk As Workbook
> > Dim DestCell As Range
> >
> > Set ActSheet = ActiveSheet
> > With ActSheet
> > Set RngToCopy = .Range("A3111:H3111")
> > End With
> >
> > 'Path to file to paste
> > Set wkbk = Workbooks.Open _
> > (Filename:="C:\Documents and Settings\jermaine_wanyou\" _
> > & "Desktop\Test_Adagio Updated\CI-Adagio-History-Web.xls")
> >
> > With wkbk.Worksheets(1) 'or wkbk.worksheets("somesheetnamehere")
> > 'next open cell in column A--I changed the .offset
> > Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
> > End With
> >
> > RngToCopy.Copy
> > DestCell.PasteSpecial Paste:=xlPasteValues, _
> > Operation:=xlNone, SkipBlanks:=False, Transpose:=False
> >
> > wkbk.Close savechanges:=True
> >
> > With ActSheet
> > Set RngToCopy = .Range("K1")
> > End With
> >
> > Set wkbk = Workbooks.Open _
> > (Filename:="C:\Documents and Settings\jermaine_wanyou\" _
> > & "Desktop\Test_Adagio Updated\Adagio-daily.xls", _
> > UpdateLinks:=0)
> >
> > With wkbk.Worksheets(1) 'or wkbk.worksheets("somesheetnamehere")
> > Set DestCell = .Range("a2")
> > End With
> >
> > RngToCopy.Copy
> > DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> > SkipBlanks:=False, Transpose:=False
> >
> > wkbk.Close savechanges:=True
> >
> > Application.CutCopyMode = False
> >
> > End Sub
> >
> >
> >
> > drinese18 wrote:
> > >
> > > Yeh I did that already but regardless I abandoned that code and wrote a
> > > different one, it works but I want it to take the last data entered on the
> > > main worksheet then copy it to the 2 other workbooks, reason for this is that
> > > the main workbook gets updated everyday, so I don't want it from just a set
> > > range but rather the last part of the workbook that is updated, my code can
> > > be seen below:
> > >
> > > Sub Copydata()
> > >
> > >
> > > Range("A3111:H3111").Select
> > > Selection.Copy
> > >
> > > 'Path to file to copy from
> > > Workbooks.Open Filename:= _
> > > "C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
> > > Updated\CI-Adagio-History-Web.xls"
> > >
> > > 'Appends data
> > > ActiveSheet.Cells(rows.count, "A").End(xlUp).Offset(0, 1).Select
> > >
> > > 'Paste Special
> > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > SkipBlanks _
> > > :=False, Transpose:=False
> > > Range("A3114").Select
> > > Application.CutCopyMode = False
> > > ActiveWorkbook.Save
> > > ActiveWindow.Close
> > > Range("A3111").Select
> > >
> > > 'Scroll to empty cell
> > > ActiveWindow.ScrollRow = 3054
> > > ActiveWindow.ScrollRow = 2572
> > > ActiveWindow.ScrollRow = 1929
> > > ActiveWindow.ScrollRow = 1528
> > > ActiveWindow.ScrollRow = 1126
> > > ActiveWindow.ScrollRow = 805
> > > ActiveWindow.ScrollRow = 563
> > > ActiveWindow.ScrollRow = 403
> > > ActiveWindow.ScrollRow = 242
> > > ActiveWindow.ScrollRow = 81
> > > ActiveWindow.ScrollRow = 1
> > > ActiveWindow.ScrollColumn = 2
> > > ActiveWindow.ScrollColumn = 3
> > > ActiveWindow.ScrollColumn = 4
> > > ActiveWindow.ScrollColumn = 5
> > >
> > > 'Copies from current date
> > > Range("K1").Select
> > > Selection.Copy
> > > Workbooks.Open Filename:= _
> > > "C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
> > > Updated\Adagio-daily.xls" _
> > > , UpdateLinks:=0
> > >
> > > 'Paste special values, date
> > > Range("A2").Select
> > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > SkipBlanks _
> > > :=False, Transpose:=False
> > > Application.CutCopyMode = False
> > > ActiveWorkbook.Save
> > > ActiveWindow.Close
> > >
> > > End Sub
> > >
> > > Hope you can help me with this, it would be really appreciated, thanks
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > This portion of the .copy line:
> > > >
> > > > Destination:=Workbooks("CI-Adagio-History-Web.xls") _
> > > > .Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 8)
> > > >
> > > > Says to start at the bottom of column A, go up to the last used cell, come down
> > > > one row and then over 8 columns.
> > > >
> > > > So I'd change that .offset(1,8) to .offset(1,0)
> > > > to stay in the same column.
> > > >
> > > > drinese18 wrote:
> > > > >
> > > > > 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
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

isn't there anyway I could do this through coding?

"Dave Peterson" wrote:

> Maybe...
>
> If you only make one entry (last used row), you could use that.
>
> If you make multiple entries, maybe you could add an indicator column that says
> this should be copied or left alone.
>
> drinese18 wrote:
> >
> > ok it works just the same as my code but the thing is I want it to copy the
> > last updated range, not necessarily A3111 to H3111, the workbook gets updated
> > everyday, so whatever was entered last I it to copy it, not just from A3111
> > to H3111 only, is there anyway to do that?
> >
> > "Dave Peterson" wrote:
> >
> > > Untested, but it did compile:
> > >
> > > Option Explicit
> > > Sub Copydata()
> > >
> > > Dim RngToCopy As Range
> > > Dim ActSheet As Worksheet
> > > Dim wkbk As Workbook
> > > Dim DestCell As Range
> > >
> > > Set ActSheet = ActiveSheet
> > > With ActSheet
> > > Set RngToCopy = .Range("A3111:H3111")
> > > End With
> > >
> > > 'Path to file to paste
> > > Set wkbk = Workbooks.Open _
> > > (Filename:="C:\Documents and Settings\jermaine_wanyou\" _
> > > & "Desktop\Test_Adagio Updated\CI-Adagio-History-Web.xls")
> > >
> > > With wkbk.Worksheets(1) 'or wkbk.worksheets("somesheetnamehere")
> > > 'next open cell in column A--I changed the .offset
> > > Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
> > > End With
> > >
> > > RngToCopy.Copy
> > > DestCell.PasteSpecial Paste:=xlPasteValues, _
> > > Operation:=xlNone, SkipBlanks:=False, Transpose:=False
> > >
> > > wkbk.Close savechanges:=True
> > >
> > > With ActSheet
> > > Set RngToCopy = .Range("K1")
> > > End With
> > >
> > > Set wkbk = Workbooks.Open _
> > > (Filename:="C:\Documents and Settings\jermaine_wanyou\" _
> > > & "Desktop\Test_Adagio Updated\Adagio-daily.xls", _
> > > UpdateLinks:=0)
> > >
> > > With wkbk.Worksheets(1) 'or wkbk.worksheets("somesheetnamehere")
> > > Set DestCell = .Range("a2")
> > > End With
> > >
> > > RngToCopy.Copy
> > > DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> > > SkipBlanks:=False, Transpose:=False
> > >
> > > wkbk.Close savechanges:=True
> > >
> > > Application.CutCopyMode = False
> > >
> > > End Sub
> > >
> > >
> > >
> > > drinese18 wrote:
> > > >
> > > > Yeh I did that already but regardless I abandoned that code and wrote a
> > > > different one, it works but I want it to take the last data entered on the
> > > > main worksheet then copy it to the 2 other workbooks, reason for this is that
> > > > the main workbook gets updated everyday, so I don't want it from just a set
> > > > range but rather the last part of the workbook that is updated, my code can
> > > > be seen below:
> > > >
> > > > Sub Copydata()
> > > >
> > > >
> > > > Range("A3111:H3111").Select
> > > > Selection.Copy
> > > >
> > > > 'Path to file to copy from
> > > > Workbooks.Open Filename:= _
> > > > "C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
> > > > Updated\CI-Adagio-History-Web.xls"
> > > >
> > > > 'Appends data
> > > > ActiveSheet.Cells(rows.count, "A").End(xlUp).Offset(0, 1).Select
> > > >
> > > > 'Paste Special
> > > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > > SkipBlanks _
> > > > :=False, Transpose:=False
> > > > Range("A3114").Select
> > > > Application.CutCopyMode = False
> > > > ActiveWorkbook.Save
> > > > ActiveWindow.Close
> > > > Range("A3111").Select
> > > >
> > > > 'Scroll to empty cell
> > > > ActiveWindow.ScrollRow = 3054
> > > > ActiveWindow.ScrollRow = 2572
> > > > ActiveWindow.ScrollRow = 1929
> > > > ActiveWindow.ScrollRow = 1528
> > > > ActiveWindow.ScrollRow = 1126
> > > > ActiveWindow.ScrollRow = 805
> > > > ActiveWindow.ScrollRow = 563
> > > > ActiveWindow.ScrollRow = 403
> > > > ActiveWindow.ScrollRow = 242
> > > > ActiveWindow.ScrollRow = 81
> > > > ActiveWindow.ScrollRow = 1
> > > > ActiveWindow.ScrollColumn = 2
> > > > ActiveWindow.ScrollColumn = 3
> > > > ActiveWindow.ScrollColumn = 4
> > > > ActiveWindow.ScrollColumn = 5
> > > >
> > > > 'Copies from current date
> > > > Range("K1").Select
> > > > Selection.Copy
> > > > Workbooks.Open Filename:= _
> > > > "C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
> > > > Updated\Adagio-daily.xls" _
> > > > , UpdateLinks:=0
> > > >
> > > > 'Paste special values, date
> > > > Range("A2").Select
> > > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > > SkipBlanks _
> > > > :=False, Transpose:=False
> > > > Application.CutCopyMode = False
> > > > ActiveWorkbook.Save
> > > > ActiveWindow.Close
> > > >
> > > > End Sub
> > > >
> > > > Hope you can help me with this, it would be really appreciated, thanks
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > This portion of the .copy line:
> > > > >
> > > > > Destination:=Workbooks("CI-Adagio-History-Web.xls") _
> > > > > .Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 8)
> > > > >
> > > > > Says to start at the bottom of column A, go up to the last used cell, come down
> > > > > one row and then over 8 columns.
> > > > >
> > > > > So I'd change that .offset(1,8) to .offset(1,0)
> > > > > to stay in the same column.
> > > > >
> > > > > drinese18 wrote:
> > > > > >
> > > > > > 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
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Only if you can describe the rules that you would use if you had to do it
manually.



drinese18 wrote:
>
> isn't there anyway I could do this through coding?
>
> "Dave Peterson" wrote:
>
> > Maybe...
> >
> > If you only make one entry (last used row), you could use that.
> >
> > If you make multiple entries, maybe you could add an indicator column that says
> > this should be copied or left alone.
> >
> > drinese18 wrote:
> > >
> > > ok it works just the same as my code but the thing is I want it to copy the
> > > last updated range, not necessarily A3111 to H3111, the workbook gets updated
> > > everyday, so whatever was entered last I it to copy it, not just from A3111
> > > to H3111 only, is there anyway to do that?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Untested, but it did compile:
> > > >
> > > > Option Explicit
> > > > Sub Copydata()
> > > >
> > > > Dim RngToCopy As Range
> > > > Dim ActSheet As Worksheet
> > > > Dim wkbk As Workbook
> > > > Dim DestCell As Range
> > > >
> > > > Set ActSheet = ActiveSheet
> > > > With ActSheet
> > > > Set RngToCopy = .Range("A3111:H3111")
> > > > End With
> > > >
> > > > 'Path to file to paste
> > > > Set wkbk = Workbooks.Open _
> > > > (Filename:="C:\Documents and Settings\jermaine_wanyou\" _
> > > > & "Desktop\Test_Adagio Updated\CI-Adagio-History-Web.xls")
> > > >
> > > > With wkbk.Worksheets(1) 'or wkbk.worksheets("somesheetnamehere")
> > > > 'next open cell in column A--I changed the .offset
> > > > Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
> > > > End With
> > > >
> > > > RngToCopy.Copy
> > > > DestCell.PasteSpecial Paste:=xlPasteValues, _
> > > > Operation:=xlNone, SkipBlanks:=False, Transpose:=False
> > > >
> > > > wkbk.Close savechanges:=True
> > > >
> > > > With ActSheet
> > > > Set RngToCopy = .Range("K1")
> > > > End With
> > > >
> > > > Set wkbk = Workbooks.Open _
> > > > (Filename:="C:\Documents and Settings\jermaine_wanyou\" _
> > > > & "Desktop\Test_Adagio Updated\Adagio-daily.xls", _
> > > > UpdateLinks:=0)
> > > >
> > > > With wkbk.Worksheets(1) 'or wkbk.worksheets("somesheetnamehere")
> > > > Set DestCell = .Range("a2")
> > > > End With
> > > >
> > > > RngToCopy.Copy
> > > > DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
> > > > SkipBlanks:=False, Transpose:=False
> > > >
> > > > wkbk.Close savechanges:=True
> > > >
> > > > Application.CutCopyMode = False
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > > drinese18 wrote:
> > > > >
> > > > > Yeh I did that already but regardless I abandoned that code and wrote a
> > > > > different one, it works but I want it to take the last data entered on the
> > > > > main worksheet then copy it to the 2 other workbooks, reason for this is that
> > > > > the main workbook gets updated everyday, so I don't want it from just a set
> > > > > range but rather the last part of the workbook that is updated, my code can
> > > > > be seen below:
> > > > >
> > > > > Sub Copydata()
> > > > >
> > > > >
> > > > > Range("A3111:H3111").Select
> > > > > Selection.Copy
> > > > >
> > > > > 'Path to file to copy from
> > > > > Workbooks.Open Filename:= _
> > > > > "C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
> > > > > Updated\CI-Adagio-History-Web.xls"
> > > > >
> > > > > 'Appends data
> > > > > ActiveSheet.Cells(rows.count, "A").End(xlUp).Offset(0, 1).Select
> > > > >
> > > > > 'Paste Special
> > > > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > > > SkipBlanks _
> > > > > :=False, Transpose:=False
> > > > > Range("A3114").Select
> > > > > Application.CutCopyMode = False
> > > > > ActiveWorkbook.Save
> > > > > ActiveWindow.Close
> > > > > Range("A3111").Select
> > > > >
> > > > > 'Scroll to empty cell
> > > > > ActiveWindow.ScrollRow = 3054
> > > > > ActiveWindow.ScrollRow = 2572
> > > > > ActiveWindow.ScrollRow = 1929
> > > > > ActiveWindow.ScrollRow = 1528
> > > > > ActiveWindow.ScrollRow = 1126
> > > > > ActiveWindow.ScrollRow = 805
> > > > > ActiveWindow.ScrollRow = 563
> > > > > ActiveWindow.ScrollRow = 403
> > > > > ActiveWindow.ScrollRow = 242
> > > > > ActiveWindow.ScrollRow = 81
> > > > > ActiveWindow.ScrollRow = 1
> > > > > ActiveWindow.ScrollColumn = 2
> > > > > ActiveWindow.ScrollColumn = 3
> > > > > ActiveWindow.ScrollColumn = 4
> > > > > ActiveWindow.ScrollColumn = 5
> > > > >
> > > > > 'Copies from current date
> > > > > Range("K1").Select
> > > > > Selection.Copy
> > > > > Workbooks.Open Filename:= _
> > > > > "C:\Documents and Settings\jermaine_wanyou\Desktop\Test_Adagio
> > > > > Updated\Adagio-daily.xls" _
> > > > > , UpdateLinks:=0
> > > > >
> > > > > 'Paste special values, date
> > > > > Range("A2").Select
> > > > > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > > > SkipBlanks _
> > > > > :=False, Transpose:=False
> > > > > Application.CutCopyMode = False
> > > > > ActiveWorkbook.Save
> > > > > ActiveWindow.Close
> > > > >
> > > > > End Sub
> > > > >
> > > > > Hope you can help me with this, it would be really appreciated, thanks
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > This portion of the .copy line:
> > > > > >
> > > > > > Destination:=Workbooks("CI-Adagio-History-Web.xls") _
> > > > > > .Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 8)
> > > > > >
> > > > > > Says to start at the bottom of column A, go up to the last used cell, come down
> > > > > > one row and then over 8 columns.
> > > > > >
> > > > > > So I'd change that .offset(1,8) to .offset(1,0)
> > > > > > to stay in the same column.
> > > > > >
> > > > > > drinese18 wrote:
> > > > > > >
> > > > > > > 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
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson