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
|
Similar Threads
