Filter and immediately overwrite same sheet with results

[artisdepartis]

Dear Guru's,

What would be the better / correct approach???!!! See my code below.
learning a lot through trail and error, I seem to be stuck again in
the latter: I have a macro in which I filter data, after which I want
to CopyPaste the data.value to the same sheet, overwriting what was
there. I have:

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Worksheets(sFileNameSource01 & lRecDate).AutoFilterMode = False
Cells.Clear
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

This gives me a 1004-error: PasteSpecial Method of Range class failed.

What would be the better / correct approach???!!!

TFH, AdP

 

[Guest]

Hi

Somthing along the lines of

With ws
.Range("A1").CurrentRegion.Copy
.Range("A1").CurrentRegion.PasteSpecial xlPasteValues
End With

untested with a filtered range

 

[Guest]

Bad advise on my part in my last post - please disregard

will continue testing

 

[artisdepartis]

Bad advise on my part in my last post - please disregard

will continue testing










- Show quoted text -

Is this even doable? I can't seem to work around an error message
regarding the size of my copy region.

 

[Guest]

I dont think that it is possible - but could well be wrong

The only way I managed to get around this was to paste the data to a
different(Temporary) location and copy/paste it back after deleting the
original.

 

[artisdepartis]

Steve, TY for trying.

I have come to the same conclusion. Although i do not like it one
bit :S
(Not that it is critical, but in my case i need to create this new
location (add a sheet) and refer to the added sheet.

AdP