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).Of fset(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).Of fset(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).Of fset(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).Of fset(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).Of fset(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).Of fset(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).Of fset(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

Basically the procedure would be where we ran a macro that downloads a line
of data from an online application. We perform this task everyday so those
values don't stay the same, however I don't want them to be overwritten, so
when I copy it and paste it into the other workbook I would normally paste it
under the last record I copied to the workbook, after I've done that then I
save it and close it

"Dave Peterson" wrote:

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).Of fset(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

If you can always use the last used row in column A:

With ActSheet
Set RngToCopy = .cells(.rows.count,"A").end(xlup).resize(1,8)
End With

drinese18 wrote:

Basically the procedure would be where we ran a macro that downloads a line
of data from an online application. We perform this task everyday so those
values don't stay the same, however I don't want them to be overwritten, so
when I copy it and paste it into the other workbook I would normally paste it
under the last record I copied to the workbook, after I've done that then I
save it and close it

"Dave Peterson" wrote:

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).Of fset(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


--

Dave Peterson

Hey thanks a lot, it works perfect

"Dave Peterson" wrote:

If you can always use the last used row in column A:

With ActSheet
Set RngToCopy = .cells(.rows.count,"A").end(xlup).resize(1,8)
End With

drinese18 wrote:

Basically the procedure would be where we ran a macro that downloads a line
of data from an online application. We perform this task everyday so those
values don't stay the same, however I don't want them to be overwritten, so
when I copy it and paste it into the other workbook I would normally paste it
under the last record I copied to the workbook, after I've done that then I
save it and close it

"Dave Peterson" wrote:

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).Of fset(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


--

Dave Peterson