Error Selecting Same Range in Different Worksheets

Clunky

New Member
Joined
Jun 18, 2013
Messages
5
'>> 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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

goesr

Well-known Member
Joined
Nov 15, 2013
Messages
649
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

New Member
Joined
Jun 18, 2013
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,333
Messages
5,595,560
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top