| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| filter results to userform-sheet | Excel Programming | |||
| Save As - Can I use VBA? to stop XL Sheet overwrite | Excel Discussion (Misc queries) | |||
| Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
| How do I link filter results into another sheet? | Excel Worksheet Functions | |||
| Overwrite Master sheet when using merge macro | Excel Programming | |||
Hybrid Mode
