Cut & Paste - AutoFilter SpecialCells(xlCellTypeVisible)

[SunOffice]

Hi All,

Can you please correct below codes and provide me a right approach for AutoFilter SpecialCells(xlCellTypeVisible)??

Problem: - The below codes are just working fine for me when the auto-filtered data is copied and pasted to a new worksheet, but I want to use the 'Cut and Paste' methodology instead of that.

Please Login or Register  to view this content.

Here I have tried with below codes to get the output as per the above description and codes. Later, in the last....This deletes the copied cells's entire row

Please Login or Register  to view this content.

...but I know it is not a good way. Please help me out to get a smart way for applying CUT and PASTE methodology on the filtered data/ cells (AutoFilter.Range.SpecialCells(xlCellTypeVisible)).

Please find the attached for better understanding:
MS Excel Version: 2007

Thanks in Advance!

[Charles]

Hi,


Here is a portion of your code to "Copy Paste". Not sure why you want to do this but....

Please Login or Register  to view this content.

[SunOffice]

Thanks for ur quick reply!

...here I'm getting a Run-time error '438': Object doesn't support this property or method, please see below of that section.

Range("A1").Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Actually I have a huge data in the main worksheet, which is maintained on a daily basis. I have to cut the values on the tomorrow's date and paste in a new worksheet and keep that data.

In my codes... I get the data in a new worksheet 'GetDATA', but it was copied, so doesn't delete copied values from the main worksheet, which is now a big problem for me.

[Charles]

Hi,


If I understand you this will now copy and delete the data.

[SunOffice]

Hi,

Thanks for ur kind efforts!

I just checked ur codes for deleting the SpecialCells(xlCellTypeVisible) of the autoFilter mode. It works fine in a better way than mine.

Please Login or Register  to view this content.

In your codes, under the below line:

Range(Cells(VisRng.Offset(0, 0).Row, 1), Cells(Range("A65536").End(xlUp).Row, 8)).EntireRow.Delete

I just changed from (VisRng.Offset(1, 0).Row, 1) to (VisRng.Offset(0, 0).Row, 1), so all is going well.


...but I don't know wht's going wrong with the Dim strName As String, When I put a future date in the inputbox; that doesn't accept the date of the current month. for example 6/10/2011, 6/15/2011, 6/30/2011, but goes okay with the next month's dates.

[Charles]

HI,

Not sure why it works for "Next" month, but I changed this line of code.
Note ">=". I changed it too what you see.

Please Login or Register  to view this content.

[SunOffice]

Hi,

I re looked at the file you posted and changed the

"If strName = vbNullString Or strName >= VBA.Date Then" back too

"If strName = vbNullString Or strName <= VBA.Date Then"

and it tested ok. I do not know why it failed before.

[SunOffice]

Thank you Mr Charles for great help in a quick time!