Have you tried your code against a small test version of the worksheets? What
happens when you try it manually?

It worked fine for me (after I commented the CheckRange stuff).

Is there any chance that the paste is failing because the "Member ID Input
Summary" worksheet is protected?

#1. There may be a limit of how many nested if's you can have. But VBA's limit
is way higher than mine! I've never seen it. But you can surely nest a few of
them without errors.

#2. If someone told you that 30 variables is the limit, then they are mistaken.



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

--

Dave Peterson