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

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

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

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

Hi Dave,

I broke it down into a small sub of it's own, manually executed it, and it
still did not work (FYI - I verified the sheet is not protected). Per my
prior post in response to Steve, the only way I can get it work is to select
the sheet and then use "cells.pastespeical..."

I shouldn't want to have to use Select to paste -- I don't understand why it
is not working as currently written.

--
Robert


"Dave Peterson" wrote:

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

Did you try it against a couple of different worksheets?



robs3131 wrote:

Hi Dave,

I broke it down into a small sub of it's own, manually executed it, and it
still did not work (FYI - I verified the sheet is not protected). Per my
prior post in response to Steve, the only way I can get it work is to select
the sheet and then use "cells.pastespeical..."

I shouldn't want to have to use Select to paste -- I don't understand why it
is not working as currently written.

--
Robert

"Dave Peterson" wrote:

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


--

Dave Peterson

Hi Dave,

The code works when I create a whole new file with just that one portion of
the code.

After testing the above, I used the "Clean Project" software to clean my
file -- that didn't correct the issues -- and then finally created a whole
new file by copying all sheets over to a new file as well as the code -- this
did not correct the issue either.

So I guess it has something to do with the number of "If/Then"s, "With"s,
and/or variables that I have. The part that really concerns me is that there
is NO error message that comes up. The code simply finishes executing, but
doesn't actually execute the paste....
--
Robert


"Dave Peterson" wrote:

Did you try it against a couple of different worksheets?



robs3131 wrote:

Hi Dave,

I broke it down into a small sub of it's own, manually executed it, and it
still did not work (FYI - I verified the sheet is not protected). Per my
prior post in response to Steve, the only way I can get it work is to select
the sheet and then use "cells.pastespeical..."

I shouldn't want to have to use Select to paste -- I don't understand why it
is not working as currently written.

--
Robert

"Dave Peterson" wrote:

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


--

Dave Peterson

Wow - what is REALLY strange is that if I change the sheet from "Member ID
Input Summary" to "Payment Sales Input", which is another sheet within the
file, the paste IS executed (this code is below - see the line highlighted
with "" -- all that was changed was the sheet name)...so to summarize,
Paste is not executed (and there is no error message) when "Member ID Input
Summary" is the sheet specified -- however, Paste IS executed when "Payment
Sales Input" is the sheet specified. Does anyone have any idea why this
would be?? Thanks.

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("Payment Sales Input")
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Set salesrange = .Range("O2", .Range("O2").End(xlDown))
For Each A In salesrange
If A.Value = A.Offset(1, 0).Value Then
A.Offset(1, -2).Value = "Duplicate of record above and
has been excluded from calculations. Note that 'Payment Transaction Report'
data input shows payment occured for this duplicate record."
Else
End If
A.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


"robs3131" wrote:

Hi Dave,

The code works when I create a whole new file with just that one portion of
the code.

After testing the above, I used the "Clean Project" software to clean my
file -- that didn't correct the issues -- and then finally created a whole
new file by copying all sheets over to a new file as well as the code -- this
did not correct the issue either.

So I guess it has something to do with the number of "If/Then"s, "With"s,
and/or variables that I have. The part that really concerns me is that there
is NO error message that comes up. The code simply finishes executing, but
doesn't actually execute the paste....
--
Robert


"Dave Peterson" wrote:

Did you try it against a couple of different worksheets?



robs3131 wrote:

Hi Dave,

I broke it down into a small sub of it's own, manually executed it, and it
still did not work (FYI - I verified the sheet is not protected). Per my
prior post in response to Steve, the only way I can get it work is to select
the sheet and then use "cells.pastespeical..."

I shouldn't want to have to use Select to paste -- I don't understand why it
is not working as currently written.

--
Robert

"Dave Peterson" wrote:

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


--

Dave Peterson

OK - I figured it out - and of course it was a complete miss on my part. I
have a button from a sheet called "Main Menu" that takes me to the "Member ID
Input Summary" sheet -- as part of the code attached to this button, it
copies and pastes data from another sheet, pasting over the data that was
pasted in the code below. I'm sorry for everyone who spent time on this!
Bear with me...I honestly do spend a lot of time troubleshooting myself
before posting here. This was a painful lesson.

Thanks,
--
Robert


"robs3131" wrote:

Wow - what is REALLY strange is that if I change the sheet from "Member ID
Input Summary" to "Payment Sales Input", which is another sheet within the
file, the paste IS executed (this code is below - see the line highlighted
with "" -- all that was changed was the sheet name)...so to summarize,
Paste is not executed (and there is no error message) when "Member ID Input
Summary" is the sheet specified -- however, Paste IS executed when "Payment
Sales Input" is the sheet specified. Does anyone have any idea why this
would be?? Thanks.

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("Payment Sales Input")
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Set salesrange = .Range("O2", .Range("O2").End(xlDown))
For Each A In salesrange
If A.Value = A.Offset(1, 0).Value Then
A.Offset(1, -2).Value = "Duplicate of record above and
has been excluded from calculations. Note that 'Payment Transaction Report'
data input shows payment occured for this duplicate record."
Else
End If
A.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


"robs3131" wrote:

Hi Dave,

The code works when I create a whole new file with just that one portion of
the code.

After testing the above, I used the "Clean Project" software to clean my
file -- that didn't correct the issues -- and then finally created a whole
new file by copying all sheets over to a new file as well as the code -- this
did not correct the issue either.

So I guess it has something to do with the number of "If/Then"s, "With"s,
and/or variables that I have. The part that really concerns me is that there
is NO error message that comes up. The code simply finishes executing, but
doesn't actually execute the paste....
--
Robert


"Dave Peterson" wrote:

Did you try it against a couple of different worksheets?



robs3131 wrote:

Hi Dave,

I broke it down into a small sub of it's own, manually executed it, and it
still did not work (FYI - I verified the sheet is not protected). Per my
prior post in response to Steve, the only way I can get it work is to select
the sheet and then use "cells.pastespeical..."

I shouldn't want to have to use Select to paste -- I don't understand why it
is not working as currently written.

--
Robert

"Dave Peterson" wrote:

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


--

Dave Peterson

Glad you found the problem.

You'll look back at this problem and laugh (in a year or two <vbg).

robs3131 wrote:

OK - I figured it out - and of course it was a complete miss on my part. I
have a button from a sheet called "Main Menu" that takes me to the "Member ID
Input Summary" sheet -- as part of the code attached to this button, it
copies and pastes data from another sheet, pasting over the data that was
pasted in the code below. I'm sorry for everyone who spent time on this!
Bear with me...I honestly do spend a lot of time troubleshooting myself
before posting here. This was a painful lesson.

Thanks,
--
Robert

"robs3131" wrote:

Wow - what is REALLY strange is that if I change the sheet from "Member ID
Input Summary" to "Payment Sales Input", which is another sheet within the
file, the paste IS executed (this code is below - see the line highlighted
with "" -- all that was changed was the sheet name)...so to summarize,
Paste is not executed (and there is no error message) when "Member ID Input
Summary" is the sheet specified -- however, Paste IS executed when "Payment
Sales Input" is the sheet specified. Does anyone have any idea why this
would be?? Thanks.

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("Payment Sales Input")
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Set salesrange = .Range("O2", .Range("O2").End(xlDown))
For Each A In salesrange
If A.Value = A.Offset(1, 0).Value Then
A.Offset(1, -2).Value = "Duplicate of record above and
has been excluded from calculations. Note that 'Payment Transaction Report'
data input shows payment occured for this duplicate record."
Else
End If
A.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


"robs3131" wrote:

Hi Dave,

The code works when I create a whole new file with just that one portion of
the code.

After testing the above, I used the "Clean Project" software to clean my
file -- that didn't correct the issues -- and then finally created a whole
new file by copying all sheets over to a new file as well as the code -- this
did not correct the issue either.

So I guess it has something to do with the number of "If/Then"s, "With"s,
and/or variables that I have. The part that really concerns me is that there
is NO error message that comes up. The code simply finishes executing, but
doesn't actually execute the paste....
--
Robert


"Dave Peterson" wrote:

Did you try it against a couple of different worksheets?



robs3131 wrote:

Hi Dave,

I broke it down into a small sub of it's own, manually executed it, and it
still did not work (FYI - I verified the sheet is not protected). Per my
prior post in response to Steve, the only way I can get it work is to select
the sheet and then use "cells.pastespeical..."

I shouldn't want to have to use Select to paste -- I don't understand why it
is not working as currently written.

--
Robert

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson