Updating Workbook
 

I want to basically create a macro that takes the final information entered
from the main workbook and then insert them or Append to the workbook. Just
like if you would go into an Excel Workbook and it would ask you to Update,
probably from the internet or from a database, I want this workbook to update
from another workbook, can anyone help me with this please?

Updating Workbook
 

You didn't give much to go on regarding your needs or how you envision
accomplishing this.

You could:

*Code it so a program opens the other workbook and copies the data
*Link cells to the other workbook
*Use ADO or DAO to make an ODBC connection and import the data

HTH

"drinese18" wrote:

I want to basically create a macro that takes the final information entered
from the main workbook and then insert them or Append to the workbook. Just
like if you would go into an Excel Workbook and it would ask you to Update,
probably from the internet or from a database, I want this workbook to update
from another workbook, can anyone help me with this please?

Updating Workbook
 

Ok I'll show you the code I have so far:


Option Explicit

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


With Worksheets("Index")
Set rng1 = .Range("A1:A" & .Range("A65536").End(xlDown).Row)
End With
Set rngFound = rng1.Find(what:=DateValue(Me.TextBox1.Value))
Workbooks.Open Filename:="C:\Documents and
Settings\jermaine_wanyou\Desktop\Test_Adagio\CI-Adagio-History-Web.xls"
Range(rngFound, rngFound.Offset(1, 8)).Copy
Workbooks("CI-Adagio-History-Web.xls").Worksheets("Sheet1").Range("A3114")
Set rngFound = Nothing
Unload Me
End
End Sub

Within the Range section that says "Range A3114", I would like it to check
to see if there is any blank cells or rows after the previous data. If there
isn't then paste the data in the empty cell, if there is data it should tell
me that there is, I was thinking about an If statement but I don't know how
to incorporate it within the code I have

"XP" wrote:

You didn't give much to go on regarding your needs or how you envision
accomplishing this.

You could:

*Code it so a program opens the other workbook and copies the data
*Link cells to the other workbook
*Use ADO or DAO to make an ODBC connection and import the data

HTH

"drinese18" wrote:

I want to basically create a macro that takes the final information entered
from the main workbook and then insert them or Append to the workbook. Just
like if you would go into an Excel Workbook and it would ask you to Update,
probably from the internet or from a database, I want this workbook to update
from another workbook, can anyone help me with this please?

Updating Workbook
 

If you mean you just want to locate the next available cell to paste the data
into safely, without overwriting anything try:

Range("A65536").End(xlUp).Offset(1, 0).Select
<your paste code here

This should work unless there is danger that the entire spreadsheet is
filled down to row 65536.

HTH

"drinese18" wrote:

Ok I'll show you the code I have so far:


Option Explicit

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


With Worksheets("Index")
Set rng1 = .Range("A1:A" & .Range("A65536").End(xlDown).Row)
End With
Set rngFound = rng1.Find(what:=DateValue(Me.TextBox1.Value))
Workbooks.Open Filename:="C:\Documents and
Settings\jermaine_wanyou\Desktop\Test_Adagio\CI-Adagio-History-Web.xls"
Range(rngFound, rngFound.Offset(1, 8)).Copy
Workbooks("CI-Adagio-History-Web.xls").Worksheets("Sheet1").Range("A3114")
Set rngFound = Nothing
Unload Me
End
End Sub

Within the Range section that says "Range A3114", I would like it to check
to see if there is any blank cells or rows after the previous data. If there
isn't then paste the data in the empty cell, if there is data it should tell
me that there is, I was thinking about an If statement but I don't know how
to incorporate it within the code I have

"XP" wrote:

You didn't give much to go on regarding your needs or how you envision
accomplishing this.

You could:

*Code it so a program opens the other workbook and copies the data
*Link cells to the other workbook
*Use ADO or DAO to make an ODBC connection and import the data

HTH

"drinese18" wrote:

I want to basically create a macro that takes the final information entered
from the main workbook and then insert them or Append to the workbook. Just
like if you would go into an Excel Workbook and it would ask you to Update,
probably from the internet or from a database, I want this workbook to update
from another workbook, can anyone help me with this please?

Updating Workbook
 

ok I tried it, I kind a figured it was something of that sort, but my problem
was how do I incorporate it into my code?

"XP" wrote:

If you mean you just want to locate the next available cell to paste the data
into safely, without overwriting anything try:

Range("A65536").End(xlUp).Offset(1, 0).Select
<your paste code here

This should work unless there is danger that the entire spreadsheet is
filled down to row 65536.

HTH

"drinese18" wrote:

Ok I'll show you the code I have so far:


Option Explicit

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


With Worksheets("Index")
Set rng1 = .Range("A1:A" & .Range("A65536").End(xlDown).Row)
End With
Set rngFound = rng1.Find(what:=DateValue(Me.TextBox1.Value))
Workbooks.Open Filename:="C:\Documents and
Settings\jermaine_wanyou\Desktop\Test_Adagio\CI-Adagio-History-Web.xls"
Range(rngFound, rngFound.Offset(1, 8)).Copy
Workbooks("CI-Adagio-History-Web.xls").Worksheets("Sheet1").Range("A3114")
Set rngFound = Nothing
Unload Me
End
End Sub

Within the Range section that says "Range A3114", I would like it to check
to see if there is any blank cells or rows after the previous data. If there
isn't then paste the data in the empty cell, if there is data it should tell
me that there is, I was thinking about an If statement but I don't know how
to incorporate it within the code I have

"XP" wrote:

You didn't give much to go on regarding your needs or how you envision
accomplishing this.

You could:

*Code it so a program opens the other workbook and copies the data
*Link cells to the other workbook
*Use ADO or DAO to make an ODBC connection and import the data

HTH

"drinese18" wrote:

I want to basically create a macro that takes the final information entered
from the main workbook and then insert them or Append to the workbook. Just
like if you would go into an Excel Workbook and it would ask you to Update,
probably from the internet or from a database, I want this workbook to update
from another workbook, can anyone help me with this please?

Updating Workbook
 

Try the following, but all as one line (test on a back up copy for safety):

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

HTH

"drinese18" wrote:

ok I tried it, I kind a figured it was something of that sort, but my problem
was how do I incorporate it into my code?

"XP" wrote:

If you mean you just want to locate the next available cell to paste the data
into safely, without overwriting anything try:

Range("A65536").End(xlUp).Offset(1, 0).Select
<your paste code here

This should work unless there is danger that the entire spreadsheet is
filled down to row 65536.

HTH

"drinese18" wrote:

Ok I'll show you the code I have so far:


Option Explicit

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


With Worksheets("Index")
Set rng1 = .Range("A1:A" & .Range("A65536").End(xlDown).Row)
End With
Set rngFound = rng1.Find(what:=DateValue(Me.TextBox1.Value))
Workbooks.Open Filename:="C:\Documents and
Settings\jermaine_wanyou\Desktop\Test_Adagio\CI-Adagio-History-Web.xls"
Range(rngFound, rngFound.Offset(1, 8)).Copy
Workbooks("CI-Adagio-History-Web.xls").Worksheets("Sheet1").Range("A3114")
Set rngFound = Nothing
Unload Me
End
End Sub

Within the Range section that says "Range A3114", I would like it to check
to see if there is any blank cells or rows after the previous data. If there
isn't then paste the data in the empty cell, if there is data it should tell
me that there is, I was thinking about an If statement but I don't know how
to incorporate it within the code I have

"XP" wrote:

You didn't give much to go on regarding your needs or how you envision
accomplishing this.

You could:

*Code it so a program opens the other workbook and copies the data
*Link cells to the other workbook
*Use ADO or DAO to make an ODBC connection and import the data

HTH

"drinese18" wrote:

I want to basically create a macro that takes the final information entered
from the main workbook and then insert them or Append to the workbook. Just
like if you would go into an Excel Workbook and it would ask you to Update,
probably from the internet or from a database, I want this workbook to update
from another workbook, can anyone help me with this please?

Updating Workbook
 

I tried the code and it works up to a point, for the "Destination" object, I
tried declaring it as Excel but it doesn't go through it jus brings up
another error, if I don't declare it, it still brings up an error, what do
you think is wrong?

"XP" wrote:

Try the following, but all as one line (test on a back up copy for safety):

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

HTH

"drinese18" wrote:

ok I tried it, I kind a figured it was something of that sort, but my problem
was how do I incorporate it into my code?

"XP" wrote:

If you mean you just want to locate the next available cell to paste the data
into safely, without overwriting anything try:

Range("A65536").End(xlUp).Offset(1, 0).Select
<your paste code here

This should work unless there is danger that the entire spreadsheet is
filled down to row 65536.

HTH

"drinese18" wrote:

Ok I'll show you the code I have so far:


Option Explicit

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


With Worksheets("Index")
Set rng1 = .Range("A1:A" & .Range("A65536").End(xlDown).Row)
End With
Set rngFound = rng1.Find(what:=DateValue(Me.TextBox1.Value))
Workbooks.Open Filename:="C:\Documents and
Settings\jermaine_wanyou\Desktop\Test_Adagio\CI-Adagio-History-Web.xls"
Range(rngFound, rngFound.Offset(1, 8)).Copy
Workbooks("CI-Adagio-History-Web.xls").Worksheets("Sheet1").Range("A3114")
Set rngFound = Nothing
Unload Me
End
End Sub

Within the Range section that says "Range A3114", I would like it to check
to see if there is any blank cells or rows after the previous data. If there
isn't then paste the data in the empty cell, if there is data it should tell
me that there is, I was thinking about an If statement but I don't know how
to incorporate it within the code I have

"XP" wrote:

You didn't give much to go on regarding your needs or how you envision
accomplishing this.

You could:

*Code it so a program opens the other workbook and copies the data
*Link cells to the other workbook
*Use ADO or DAO to make an ODBC connection and import the data

HTH

"drinese18" wrote:

I want to basically create a macro that takes the final information entered
from the main workbook and then insert them or Append to the workbook. Just
like if you would go into an Excel Workbook and it would ask you to Update,
probably from the internet or from a database, I want this workbook to update
from another workbook, can anyone help me with this please?