Hi,

I'm trying to take data from a spreadsheet by copying it, and then open a
new spreadsheet (that is saved on the c:\ drive), find the last row of data
in the spreadsheet, paste the data from the original spreadsheet, and then
save and close the database sheet.

I know this would probably be more efficient with Access, but the users will
not have access to Access, so I need to use Excel.

Any help with coding would be greatly appreciated.

Thanks,

Brian

See this page
http://www.rondebruin.nl/copy1.htm

See the last example that use another workbook as database

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" wrote in message ...
Hi,

I'm trying to take data from a spreadsheet by copying it, and then open a
new spreadsheet (that is saved on the c:\ drive), find the last row of data
in the spreadsheet, paste the data from the original spreadsheet, and then
save and close the database sheet.

I know this would probably be more efficient with Access, but the users will
not have access to Access, so I need to use Excel.

Any help with coding would be greatly appreciated.

Thanks,

Brian

"Ron de Bruin" wrote:

See this page
http://www.rondebruin.nl/copy1.htm

See the last example that use another workbook as database

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" wrote in message ...
Hi,

I'm trying to take data from a spreadsheet by copying it, and then open a
new spreadsheet (that is saved on the c:\ drive), find the last row of data
in the spreadsheet, paste the data from the original spreadsheet, and then
save and close the database sheet.

I know this would probably be more efficient with Access, but the users will
not have access to Access, so I need to use Excel.

Any help with coding would be greatly appreciated.

Thanks,

Brian


Hi Ron,

Thanks for the response. I tried the code on your web page, but I still
can't get it to select the first empty row after the last row of data. For
some reason it keeps goint to row 21.

BTW I'm using Excel 2000.

Here's the code I wrote so far:

Sub SaveCustomerInformation()
'
' SaveCustomerInformation Macro
'
' Keyboard Shortcut: Ctrl+n
'
Dim source As Range
Dim destwb As Workbook
Sheets("Sheet3").Visible = True
Sheets("Sheet3").Select
Set source = Range("A2:cg2")
source.Select
Selection.Copy
Workbooks.Open Filename:="c:\datafile.xls"
Windows("datafile.xls").Activate
Range("a1").Select
Dim destrange As Range
Dim Lr As Long
Lr = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Set destrange = Range("A" & Lr)
destrange.Select
destrange.PasteSpecial xlPasteValues
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("Inputsheet.xls").Activate
ActiveSheet.Select
Sheets(9).Visible = xlVeryHidden
End Sub

You not use the code from the site

This Example will do the same as the first example on this webpage
Only the database sheet is in another workbook.
The macro will open the database workbook if it is not open (It use the function to check if the workbook is already open)
The data will be paste as values in the first worksheet of the file "c:\test.xls"


Sub copy_to_another_workbook()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("test.xls") Then
Set destWB = Workbooks("test.xls")
Else
Set destWB = Workbooks.Open("c:\test.xls")
End If
Lr = LastRow(destWB.Worksheets("Sheet1")) + 1
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")
Set destrange = destWB.Worksheets("Sheet1").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub


Copy this function together with the LastRow function in the module

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" wrote in message ...


"Ron de Bruin" wrote:

See this page
http://www.rondebruin.nl/copy1.htm

See the last example that use another workbook as database

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" wrote in message ...
Hi,

I'm trying to take data from a spreadsheet by copying it, and then open a
new spreadsheet (that is saved on the c:\ drive), find the last row of data
in the spreadsheet, paste the data from the original spreadsheet, and then
save and close the database sheet.

I know this would probably be more efficient with Access, but the users will
not have access to Access, so I need to use Excel.

Any help with coding would be greatly appreciated.

Thanks,

Brian


Hi Ron,

Thanks for the response. I tried the code on your web page, but I still
can't get it to select the first empty row after the last row of data. For
some reason it keeps goint to row 21.

BTW I'm using Excel 2000.

Here's the code I wrote so far:

Sub SaveCustomerInformation()
'
' SaveCustomerInformation Macro
'
' Keyboard Shortcut: Ctrl+n
'
Dim source As Range
Dim destwb As Workbook
Sheets("Sheet3").Visible = True
Sheets("Sheet3").Select
Set source = Range("A2:cg2")
source.Select
Selection.Copy
Workbooks.Open Filename:="c:\datafile.xls"
Windows("datafile.xls").Activate
Range("a1").Select
Dim destrange As Range
Dim Lr As Long
Lr = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Set destrange = Range("A" & Lr)
destrange.Select
destrange.PasteSpecial xlPasteValues
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("Inputsheet.xls").Activate
ActiveSheet.Select
Sheets(9).Visible = xlVeryHidden
End Sub

"Ron de Bruin" wrote:

You not use the code from the site

This Example will do the same as the first example on this webpage
Only the database sheet is in another workbook.
The macro will open the database workbook if it is not open (It use the function to check if the workbook is already open)
The data will be paste as values in the first worksheet of the file "c:\test.xls"


Sub copy_to_another_workbook()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("test.xls") Then
Set destWB = Workbooks("test.xls")
Else
Set destWB = Workbooks.Open("c:\test.xls")
End If
Lr = LastRow(destWB.Worksheets("Sheet1")) + 1
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")
Set destrange = destWB.Worksheets("Sheet1").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub


Copy this function together with the LastRow function in the module

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" wrote in message ...


"Ron de Bruin" wrote:

See this page
http://www.rondebruin.nl/copy1.htm

See the last example that use another workbook as database

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" wrote in message ...
Hi,

I'm trying to take data from a spreadsheet by copying it, and then open a
new spreadsheet (that is saved on the c:\ drive), find the last row of data
in the spreadsheet, paste the data from the original spreadsheet, and then
save and close the database sheet.

I know this would probably be more efficient with Access, but the users will
not have access to Access, so I need to use Excel.

Any help with coding would be greatly appreciated.

Thanks,

Brian


Hi Ron,

Thanks for the response. I tried the code on your web page, but I still
can't get it to select the first empty row after the last row of data. For
some reason it keeps goint to row 21.

BTW I'm using Excel 2000.

Here's the code I wrote so far:

Sub SaveCustomerInformation()
'
' SaveCustomerInformation Macro
'
' Keyboard Shortcut: Ctrl+n
'
Dim source As Range
Dim destwb As Workbook
Sheets("Sheet3").Visible = True
Sheets("Sheet3").Select
Set source = Range("A2:cg2")
source.Select
Selection.Copy
Workbooks.Open Filename:="c:\datafile.xls"
Windows("datafile.xls").Activate
Range("a1").Select
Dim destrange As Range
Dim Lr As Long
Lr = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Set destrange = Range("A" & Lr)
destrange.Select
destrange.PasteSpecial xlPasteValues
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("Inputsheet.xls").Activate
ActiveSheet.Select
Sheets(9).Visible = xlVeryHidden
End Sub


Ron,

Thank you very much, this worked great, but created another question. I
need a way to avoid entry of duplicate records in the Excel Database. i.e.
if the user saves the same record, I want to look at the database, and ask
them if they want to overwrite the existing data or create a new one.

Thanks again,

Brian

Hi Brian

Do you have one column that we can use to test for the duplicate


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" wrote in message ...


"Ron de Bruin" wrote:

You not use the code from the site

This Example will do the same as the first example on this webpage
Only the database sheet is in another workbook.
The macro will open the database workbook if it is not open (It use the function to check if the workbook is already open)
The data will be paste as values in the first worksheet of the file "c:\test.xls"


Sub copy_to_another_workbook()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("test.xls") Then
Set destWB = Workbooks("test.xls")
Else
Set destWB = Workbooks.Open("c:\test.xls")
End If
Lr = LastRow(destWB.Worksheets("Sheet1")) + 1
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")
Set destrange = destWB.Worksheets("Sheet1").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub


Copy this function together with the LastRow function in the module

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" wrote in message ...


"Ron de Bruin" wrote:

See this page
http://www.rondebruin.nl/copy1.htm

See the last example that use another workbook as database

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" wrote in message ...
Hi,

I'm trying to take data from a spreadsheet by copying it, and then open a
new spreadsheet (that is saved on the c:\ drive), find the last row of data
in the spreadsheet, paste the data from the original spreadsheet, and then
save and close the database sheet.

I know this would probably be more efficient with Access, but the users will
not have access to Access, so I need to use Excel.

Any help with coding would be greatly appreciated.

Thanks,

Brian


Hi Ron,

Thanks for the response. I tried the code on your web page, but I still
can't get it to select the first empty row after the last row of data. For
some reason it keeps goint to row 21.

BTW I'm using Excel 2000.

Here's the code I wrote so far:

Sub SaveCustomerInformation()
'
' SaveCustomerInformation Macro
'
' Keyboard Shortcut: Ctrl+n
'
Dim source As Range
Dim destwb As Workbook
Sheets("Sheet3").Visible = True
Sheets("Sheet3").Select
Set source = Range("A2:cg2")
source.Select
Selection.Copy
Workbooks.Open Filename:="c:\datafile.xls"
Windows("datafile.xls").Activate
Range("a1").Select
Dim destrange As Range
Dim Lr As Long
Lr = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Set destrange = Range("A" & Lr)
destrange.Select
destrange.PasteSpecial xlPasteValues
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("Inputsheet.xls").Activate
ActiveSheet.Select
Sheets(9).Visible = xlVeryHidden
End Sub


Ron,

Thank you very much, this worked great, but created another question. I
need a way to avoid entry of duplicate records in the Excel Database. i.e.
if the user saves the same record, I want to look at the database, and ask
them if they want to overwrite the existing data or create a new one.

Thanks again,

Brian