LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 144
Default Paste not working in With statement - do I have too many 'With's?

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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Paste and Paste Special No Longer Working - Excel 2003 SheriJ Excel Discussion (Misc queries) 2 January 15th 09 09:23 PM
IF Statement not working Barrett M Excel Worksheet Functions 8 May 6th 08 03:29 PM
IF statement not working TJAC Excel Discussion (Misc queries) 2 January 13th 06 01:08 PM
If statement not working excelnut1954 Excel Programming 6 December 15th 05 09:38 PM
For Each Statement still not working Jacqui Excel Programming 6 November 2nd 05 04:01 PM


All times are GMT +1. The time now is 04:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"