Hi Brian

The range is not valid in your code and I don't know if the sheet name is correct with the space between the t and 3

Sheets("Sheet 3").Range("A:7")

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


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

I realize it's about 8:00pm. I tried the code below, and got a Syntax error
on the Set rng= ... command.

With destWB.Sheets("Sheet 3").Range("A:7")
Set rng = .Find(What:=sourceRange.Cells(1).Value,_
After:=.Cells(.Cells.Count), _


Thanks,

Brian

"Ron de Bruin" wrote:

Hi Brian

Try this one that copy A1:H1 to the Database workbook

Sub copy_to_another_workbook_test()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long
Dim rng As Range
Dim answer

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:H1")
Set destrange = destWB.Worksheets("Sheet1").Range("A" & Lr)


With destWB.Sheets("Sheet1").Range("A:A")
Set rng = .Find(What:=sourceRange.Cells(1).Value, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


If Not rng Is Nothing Then
answer = MsgBox("Do you want to overwrite the existing data ", vbYesNo, "something")
If answer = vbYes Then
sourceRange.Copy
rng.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Else
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
End If

Else
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
End If
End With

Application.ScreenUpdating = True

End Sub


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


"Brian C" wrote in message ...


Ron,

Enjoy your meal. Thanks alot for all your help.

Brian