Hi Steve - I tried the following - still didn't work though. Please note
that I verified that the copy statement (shown in my original post below) is
being executed correctly. I did the code to work by using "Select" to select
the worksheet to paste into instead of using the With statement (see item 3
below with this code that worked).....any ideas on why the With is not
working? I want to use With instead of using Select. Thanks for your help.

1 - I changed the following, but it did not work:

Old Code:
With Sheets("Member ID Input Summary")
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

New Code:
With Sheets("Member ID Input Summary")
.Range("A1:IV65536").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

2 - I changed the following, but it did not work:

Old Code:
With Sheets("Member ID Input Summary")
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

New Code:
With Sheets("Member ID Input Summary")
.Range("A1").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

3 - The following did work, but I would rather use With than have to Select
the worksheet in order to paste. The "old" code shown above is used in other
places in my code and it works correctly.

Old Code:
With Sheets("Member ID Input Summary")
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

New Code:
Sheets("Member ID Input Summary").Select
Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

--
Robert


"steve_doc" wrote:

what immediatly jumps out at me is there is no Range specified

With Sheets("Member ID Input Summary")

Either specify a range or a cell reference on that sheet



"robs3131" wrote:

Hi

For some reason, below is not executing properly in my code. The code
finishes executing without error but does not actually paste (see the
statement with "" below). FYI -- I've validated that the code goes
through the loop where the Paste occurs (I put "Stop" after the '.cells.copy'
statement -- the sheet clearly shows it has been copied).

I'm wondering if I have one of the following issues (maybe both?). If I
need to redesign that is fine (after I get it working I'm planning on
attempting to use arrays instead of ranges), but there has been no error
message. I want to make sure I understand why the Paste is not occurring
first before making any changes. Thanks for your help in advance!

1 - Is there a limit to the number of With statements and/or embedded With
statements ("With"s within "With"s) you can have? I have a bunch...there are
~35 "With" statements up through the below point in two subs that I'm
executing back to back when a button is clicked (some of which have two
layers deep of embedded Withs)

2 - I have a total of 26 variables (9 range variables, the rest with no
variable type specified -- I believe they are variants by default?) up until
this point in the code. There are another 13 variables that occur in two
subsequent subs (3 ranges, 10 variants) that run back to back after the sub
in questoin ends. I'm guessing this may be the issue as I've seen other
posts where I believe it was said 30 variables or declarations) were the
limit, but again, I do not get an error message.

Code:

With Sheets("Transaction Summary")
.Cells.Sort Key1:=.Range("O2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

If Len(.Range("O3")) < 0 Then
With .Range("O2", .Range("O2").End(xlDown))
.Formula = .Value
End With
.Cells.Copy
With Sheets("Member ID Input Summary")
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set checkrange = .Range("O2", .Range("O2").End(xlDown))
For Each H In checkrange
If H.Value = H.Offset(1, 0).Value Then
H.Offset(1, -2).Value = "Duplicate"

Else
End If
H.ClearContents
Next
End With
With Sheets("Transaction Summary")
.Range("O2", .Range("O2").End(xlDown)).ClearContents
.Cells.Sort Key1:=.Range("M2"), Order1:=xlAscending,
Header:=xlYes, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
Else
End If
End With



----
Robert