Filter and immediately overwrite same sheet with results
 

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

Filter and immediately overwrite same sheet with results
 

Hi

Somthing along the lines of

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

untested with a filtered range

"artisdepartis" wrote:

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

Filter and immediately overwrite same sheet with results
 

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

will continue testing

"artisdepartis" wrote:

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

Filter and immediately overwrite same sheet with results
 

On Jun 21, 12:48 pm, steve_doc
wrote:
Bad advise on my part in my last post - please disregard

will continue testing



"artisdepartis" wrote:
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- Hide quoted text -

- Show quoted text -

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

Filter and immediately overwrite same sheet with results
 

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" wrote:

On Jun 21, 12:48 pm, steve_doc
wrote:
Bad advise on my part in my last post - please disregard

will continue testing



"artisdepartis" wrote:
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- Hide quoted text -

- Show quoted text -

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

Filter and immediately overwrite same sheet with results
 

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

On Jun 22, 3:41 pm, steve_doc
wrote:
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" wrote:
On Jun 21, 12:48 pm, steve_doc
wrote:
Bad advise on my part in my last post - please disregard

will continue testing

"artisdepartis" wrote:
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- Hide quoted text -

- Show quoted text -

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

- Show quoted text -