I keep getting this error message "PasteSpecial method of Range class failed"
in the code below at the line: "Selection.PasteSpecial Paste:=xlPasteAll" and
I can't figure out what the problem is.
Any ideas?

For Each c In insiteWS.Range("A" & insiteCurrRow & ":A" & insitelastRow &
"").Cells
If c.Value = marketName And c.Offset(0, 1).Value = NLP Then
insiteWS.Select
insiteWS.Range("A" & insiteCurrRow & ":" & insitelastColumn &
insiteCurrRow).Select
Selection.Copy
marketWS.Select
Range("A" & marketCurrRow).Select
Selection.PasteSpecial Paste:=xlPasteAll
marketCurrRow = marketCurrRow + 1
insiteCurrRow = insiteCurrRow + 1
Else
Exit For
End If
Next c

What happens if you do the same steps manually?

Do you get the error?

Maybe you have a worksheet event that fires when you activate/deactivate a
sheet--or when you change selection. Most macros will clear the contents of the
clipboard when they run.

Is the worksheet getting pasted protected?

Does either of the ranges include merged cells?

=======
If it's not one of those, I'd do:

'with your other declarations...
dim RngToCopy as range
Dim DestCell as range

....other code here

with insitews
for each c in .range("A" & insitecurrrow & ":A" & insitelastrow).cells
if c.value = marketname _
and c.offset(0,1).value = nlp then
set rngtocopy = .range("a" & insitecurrrow _
& ":" & insitelastcolumn & insitecurrrow)

with marketws
set destcell = .range("A" & marketcurrrow)
end with

rngtocopy.copy
destcell.pastespecial Paste:=xlPasteAll

marketCurrRow = marketCurrRow + 1
insiteCurrRow = insiteCurrRow + 1
Else
Exit For
End If
End with

(Untested, uncompiled. Watch for typos.)


Ayo wrote:

I keep getting this error message "PasteSpecial method of Range class failed"
in the code below at the line: "Selection.PasteSpecial Paste:=xlPasteAll" and
I can't figure out what the problem is.
Any ideas?

For Each c In insiteWS.Range("A" & insiteCurrRow & ":A" & insitelastRow &
"").Cells
If c.Value = marketName And c.Offset(0, 1).Value = NLP Then
insiteWS.Select
insiteWS.Range("A" & insiteCurrRow & ":" & insitelastColumn &
insiteCurrRow).Select
Selection.Copy
marketWS.Select
Range("A" & marketCurrRow).Select
Selection.PasteSpecial Paste:=xlPasteAll
marketCurrRow = marketCurrRow + 1
insiteCurrRow = insiteCurrRow + 1
Else
Exit For
End If
Next c

--

Dave Peterson

I have a activate event and change event in the marketWS worksheet. Could
that be why? And how do I fix the problem?
Thanks.

"Dave Peterson" wrote:

What happens if you do the same steps manually?

Do you get the error?

Maybe you have a worksheet event that fires when you activate/deactivate a
sheet--or when you change selection. Most macros will clear the contents of the
clipboard when they run.

Is the worksheet getting pasted protected?

Does either of the ranges include merged cells?

=======
If it's not one of those, I'd do:

'with your other declarations...
dim RngToCopy as range
Dim DestCell as range

....other code here

with insitews
for each c in .range("A" & insitecurrrow & ":A" & insitelastrow).cells
if c.value = marketname _
and c.offset(0,1).value = nlp then
set rngtocopy = .range("a" & insitecurrrow _
& ":" & insitelastcolumn & insitecurrrow)

with marketws
set destcell = .range("A" & marketcurrrow)
end with

rngtocopy.copy
destcell.pastespecial Paste:=xlPasteAll

marketCurrRow = marketCurrRow + 1
insiteCurrRow = insiteCurrRow + 1
Else
Exit For
End If
End with

(Untested, uncompiled. Watch for typos.)


Ayo wrote:

I keep getting this error message "PasteSpecial method of Range class failed"
in the code below at the line: "Selection.PasteSpecial Paste:=xlPasteAll" and
I can't figure out what the problem is.
Any ideas?

For Each c In insiteWS.Range("A" & insiteCurrRow & ":A" & insitelastRow &
"").Cells
If c.Value = marketName And c.Offset(0, 1).Value = NLP Then
insiteWS.Select
insiteWS.Range("A" & insiteCurrRow & ":" & insitelastColumn &
insiteCurrRow).Select
Selection.Copy
marketWS.Select
Range("A" & marketCurrRow).Select
Selection.PasteSpecial Paste:=xlPasteAll
marketCurrRow = marketCurrRow + 1
insiteCurrRow = insiteCurrRow + 1
Else
Exit For
End If
Next c

--

Dave Peterson
.

I would bet money on it--well, if those macros are non-trivial.

I'd try the code that I suggested in the earlier post. No selecting, no
activating.

But if you want to stop the worksheet_change event on the marketws to not fire,
you can stop it this way:

application.enableevents = false
rngtocopy.copy
destcell.pastespecial Paste:=xlPasteAll
application.enableevents = true

Ayo wrote:

I have a activate event and change event in the marketWS worksheet. Could
that be why? And how do I fix the problem?
Thanks.

"Dave Peterson" wrote:

What happens if you do the same steps manually?

Do you get the error?

Maybe you have a worksheet event that fires when you activate/deactivate a
sheet--or when you change selection. Most macros will clear the contents of the
clipboard when they run.

Is the worksheet getting pasted protected?

Does either of the ranges include merged cells?

=======
If it's not one of those, I'd do:

'with your other declarations...
dim RngToCopy as range
Dim DestCell as range

....other code here

with insitews
for each c in .range("A" & insitecurrrow & ":A" & insitelastrow).cells
if c.value = marketname _
and c.offset(0,1).value = nlp then
set rngtocopy = .range("a" & insitecurrrow _
& ":" & insitelastcolumn & insitecurrrow)

with marketws
set destcell = .range("A" & marketcurrrow)
end with

rngtocopy.copy
destcell.pastespecial Paste:=xlPasteAll

marketCurrRow = marketCurrRow + 1
insiteCurrRow = insiteCurrRow + 1
Else
Exit For
End If
End with

(Untested, uncompiled. Watch for typos.)


Ayo wrote:

I keep getting this error message "PasteSpecial method of Range class failed"
in the code below at the line: "Selection.PasteSpecial Paste:=xlPasteAll" and
I can't figure out what the problem is.
Any ideas?

For Each c In insiteWS.Range("A" & insiteCurrRow & ":A" & insitelastRow &
"").Cells
If c.Value = marketName And c.Offset(0, 1).Value = NLP Then
insiteWS.Select
insiteWS.Range("A" & insiteCurrRow & ":" & insitelastColumn &
insiteCurrRow).Select
Selection.Copy
marketWS.Select
Range("A" & marketCurrRow).Select
Selection.PasteSpecial Paste:=xlPasteAll
marketCurrRow = marketCurrRow + 1
insiteCurrRow = insiteCurrRow + 1
Else
Exit For
End If
Next c

--

Dave Peterson
.


--

Dave Peterson