Error Selecting Same Range in Different Worksheets

[Clunky]

'>> The following code should copy sales order values from "Pricing Activity Log" and "Completed Pricing Log" into new "DupTest" sheet to determine if there are duplicates.
'>> Always get error in Section 4) when making selection.

Sub AddSheetDupTest()


Dim ws As Worksheet
Dim today As Date
Dim PALD3 As Range
Dim CPLD3 As Range


On Error GoTo ErrMsg


today = Date
Set PALD3 = Sheets("Pricing Activity Log").Range("D3")
Set CPLD3 = Sheets("Completed Pricing Log").Range("D3")


'1)
Set ws = Sheets.Add
ws.Name = "DupTest"


'2)
Sheets("Pricing Activity Log").Activate
PALD3.Select
Range(PALD3, Selection.End(xlDown)).Select
Selection.Copy

'3)
Sheets("DupTest").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


'4)
Sheets("Completed Pricing Log").Activate
CPLD3.Select

'ALWAYS ERRORS BELOW: IS THIS DUE TO THE SIMILARITY TO THE ABOVE PALD3 SELECTION STATEMENT?
'HAVE TRIED VARIOUS STANDARD SELECTION STATEMENTS WITH AND WITHOUT NAMING RANGES BUT GET SAME ERROR (1004) EACH TIME



Range(CPLD3, Selection.End(xlDown)).Select
Selection.Copy

'5)
Sheets("DupTest").Activate
Range("A1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'6)
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


'7)
'DUPLICATE RECORD IDENTIFICATION CODE WILL GO HERE
'-------------------------------------------------


'8)
ws.Name = "DupTest " & today




ErrMsg:
If Err.Number <> 0 Then
MsgBox ("Error #" & Str(Err.Number) & " occurred.")
Exit Sub
End If

Resume Next




End Sub

 

[XLfriend777]

Hi Clunky - As nobody has tried this yet I thought I would throw out an idea. You might check the Named Range "CPLD3" to see how it is defined. Go to the Name Manager and determine if your ranges are defined for the workbook or just for the sheet that they are on. If you try to use a Named Range in a sheet it is not defined for, this can be a problem. Hope this helps.

 

[Clunky]

Thanks for the response, goesr. I had given up, but you got me back on the problem...

This code doesn't show in Name Manager as far as I can tell, but yes, the problem was rooted in Names versus Range references. All of the sheet and range jumping apparently confused my buddy Excel (we're good again). I made it work by simply adding the Sheet Name to each and every working range reference, as opposed to defining all working ranges. Crude, but effective.

For potential future viewers, a short example of the working code follows:
'----------------------------------------------------------------------


'1) add new sheet to compare duplicates
Set ws = Sheets.Add
ws.Name = "DupTest"
Sheets("DupTest").Activate
Sheets("DupTest").Range("A1").Select


'2) copy "Pricing Activity Log" values
Sheets("Pricing Activity Log").Activate
Sheets("Pricing Activity Log").Range("D3").Select
Sheets("Pricing Activity Log").Range("D3", Selection.End(xlDown)).Select
Selection.Copy


'3) paste into "DupTest" sheet
Sheets("DupTest").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("DupTest").Range("A1").Select


'4) copy "Completed Pricing Log" values
Sheets("Completed Pricing Log").Activate
Sheets("Completed Pricing Log").Range("D3").Select
Sheets("Completed Pricing Log").Range("D3", Selection.End(xlDown)).Select
Selection.Copy

'5) On and on, you get the picture...


End Sub
'----------------------------------------------------------------------

Stating all ranges WITH the sheet name solved this problem. Thanks again goesr and Mr. Excel community!!!
Clunky