This portion:

With destWB
Set destRange _
= cells(Rows.Count, "B").End(xlUp).Offset(1, 0).EntireRow
End With

is a problem.

When you have those unqualified ranges in a procedure in a general module, then
those ranges refer to the activesheet. If you're lucky, the correct sheet is
active. If you're not so lucky, then you won't get what you want. And the
"with destwb" isn't really doing anything.

But I had a problem in my code:

With destWB
Set destRange _
= .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).EntireRow
End With

should have been:

With destWB.worksheets("whateverworksheetname")
Set destRange _
= .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).EntireRow
End With

And this portion:

With ThisWorkbook 'source workbook?
Set sourceRange _
= .Worksheets("SP").Cells(.Rows.Count, "B").End(xlUp).EntireRow
End With

should have been:

With ThisWorkbook.worksheets("SP")
Set sourceRange _
= .Cells(.Rows.Count, "B").End(xlUp).EntireRow
End With


Sorry about the errors.


Oreg wrote:

Hi Dave,

I'm not sure why, but when I leave the dots, I keep getting the
following error:
"Method or data member not found"

As soon as I took the dots out, it worked fine. Any ideas why ? I'm
just curious because I'd like to get to the point....eventually...where
I actually know what I'm doing. Here's what the code looks like that
works error free for me.

Sub CopytoSP_history()

Dim sourceRange As Range
Dim destRange As Range
Dim destWB As Workbook

Application.ScreenUpdating = False

If bIsBookOpen("SPHistory.xls") Then
Set destWB = Workbooks("SPHistory.xls")
Else
Set destWB = Workbooks.Open("C:\EVENT TRACKER\TrackerLog\" & _
"METRO\SPhistory\SPHistory.xls")
End If

With destWB
Set destRange _
= cells(Rows.Count, "B").End(xlUp).Offset(1, 0).EntireRow
End With

With ThisWorkbook 'source workbook?
Set sourceRange _
= .Worksheets("SP").cells(Rows.Count, "B").End(xlUp).EntireRow
End With

sourceRange.Copy
destRange.PasteSpecial xlPasteValues, , False, False

Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub

Thanks,

Oreg

--
Oreg
------------------------------------------------------------------------
Oreg's Profile: http://www.excelforum.com/member.php...fo&userid=9195
View this thread: http://www.excelforum.com/showthread...hreadid=480972

--

Dave Peterson