PDA

View Full Version : Solved: infinite paste!!!!



CatDaddy
06-02-2011, 12:39 PM
i have several filters that are now successfully copying:

MNPRng.AutoFilter Field:=4, Criteria1:=MNP, Operator:=xlFilterValues

Set MNPRng = MNPRng.SpecialCells(xlCellTypeVisible)
MNPRng.Copy Destination:=ActiveWorkbook.Worksheets(i).Range("A:AL")


but when i change the filter on one instance to:



MNPRng.AutoFilter Field:=4, Criteria1:=MNP, Operator:=xlOr, Criteria2:=""

Set MNPRng = MNPRng.SpecialCells(xlCellTypeVisible)
MNPRng.Copy Destination:=ActiveWorkbook.Worksheets(i).Range("A:AL")


it performs the same successful copy, but this time it pastes the same set of data one after another on the same sheet until it fills the page, then the rest of the program executes...

any ideas? :) thanks

Chabu
06-02-2011, 01:12 PM
Just to be sure, this code is not included or triggered by a change event?

CatDaddy
06-02-2011, 01:22 PM
Just to be sure, this code is not included or triggered by a change event?

no it is not!

Paul_Hossler
06-02-2011, 02:00 PM
At a guess, I'd suspect that you're selecting cells = MNP OR empty cells, and then pasting them

A small sample workbook showing the sititation would help


Criteria1:=MNP, Operator:=xlOr, Criteria2:=""


Paul

CatDaddy
06-02-2011, 02:08 PM
yes that is what i am doing, basically it filters one collumn so all that are left is the MNP value, in this case "BLANK" or empty cells (criteria2"")

the problem is not that it doesnt copy, or that it copies the wrong thing, it just pastes the copied selection an infinite number of times in the destination page...it must be the added second criteria because all the other lines work but im not sure

frank_m
06-02-2011, 04:41 PM
Seems to me that your copy to range is all of the rows in the sheet from Column A to AL,
causing the copy range to be duplicated until every row in the sheet has been filled..

What happens if you change:
MNPRng.Copy Destination:=ActiveWorkbook.Worksheets(i).Range("A:AL") To:

MNPRng.Copy Destination:=ActiveWorkbook.Worksheets(i).Range("A1:AL1") Or even:
MNPRng.Copy Destination:=ActiveWorkbook.Worksheets(i).Range("A1")

CatDaddy
06-02-2011, 04:48 PM
Seems to me that your copy to range is all of the rows in the sheet from Column A to L,
causing the copy range to be duplicated until every row in the sheet has been filled..

What happens if you change:
MNPRng.Copy Destination:=ActiveWorkbook.Worksheets(i).Range("A:AL") To:

MNPRng.Copy Destination:=ActiveWorkbook.Worksheets(i).Range("A1:AL1") Or even:
MNPRng.Copy Destination:=ActiveWorkbook.Worksheets(i).Range("A1")


YES! thank you so much...the problem is always in the last place you look lol

p45cal
06-02-2011, 10:17 PM
...the problem is always in the last place you look lolyou'd look fairly silly if, having found the problem, you kept on looking in more places, no?

CatDaddy
06-03-2011, 09:41 AM
you'd look fairly silly if, having found the problem, you kept on looking in more places, no?

- words of wisdom courtesy of my personal hero, homer simpson:rotlaugh: