This is eating my brain: Find,Copy,Paste criteria is variable date.

[Clayraw]

Hi!

First of all: Thank you for this community. It has helped me a million times in previous issues, but this one is eating my brain for nearly 3 days. Please see attached file.

My intention is to create macro that will go through column B of the data sheet and looks for today's date (cell A1 in "Today" sheet) as the criteria. Today's date will be changeable through the "buttons" that I added already with a DateAdd function. If it finds the criteria, it should copy the rows to the "Today" sheet and paste it from row X=2 onwards. I have searched macro's for hours, tried to edit them for hours, but so far no results.

*Even a better solution would be to create an inputbox which will be used as the criteria, in dd/mmm/yy format.

If you require any additional information, please let me know.

Many thanks in advance!

Clayraw

[nilem]

Hi Clayraw,
try it

Please Login or Register  to view this content.

[Clayraw]

Hi Nilem,

Thanks for the quick reply and solution! However, is it possible that it doesn't clear the data from the data sheet but just copy it? How should I add a loop so that it searches the data sheet till the last entry of the specified date? Is it possible to specify the columns that have to be copied instead of copying the whole row?

Thanks again!

[Clayraw]

*edit. Sorry. I saw it worked already. However, could you tell me how to change the cell range? this time the dates are in the Data sheet, column C. the data is starting from A2 in the same sheet. the data should be pasted from cell A3 onwards in the "Today" sheet.

Thanks

[nilem]

try

Please Login or Register  to view this content.

[Clayraw]

It didn't work out as planned somehow. I started editing your code and tried to make the criteria1 based on cell value x1 (variable date). What happens now is that it doesn't filter correctly but just pastes all data in my "Today" sheet and deletes it directly as well. So I end up with nothing at all. Could someone have a look at the things I did wrong?

Please Login or Register  to view this content.

[xlbiznes]

Hi,

Attached is my solution and i have modified it to suit your requirements.

Firstly , click on the Select file to select your actual data source and then enter the date for which you want to extract the data in the cell ("G2") and then click on process.

Hope this helps.

[nilem]

Clayraw, could you show an example of your file with the actual location of the data

[Clayraw]

Please see attached.

Many thanks

[nilem]

it is not clear what data (of which columns) you want to copy to the sheet "Today"

[Clayraw]

the data sheet is on sheet "Data", column A till O with range A2:O10000 since the data entries depend on no. of sellings per period of time

I would like to autofilter that range with cell X1 on the "data" sheet as the criteria and then copy the entire columns to the "today" sheet, pasting from cell A2 onwards.

[nilem]

try

Please Login or Register  to view this content.

[Clayraw]

Unfortunately it is only copying the column headers, but not grabbing the data below. It seems like the auto filter is not filtering, since I checked if I have any entries for multiple dates. I tried the alternatives as well (which were comments first) but it had the same issue.

The formatting of the date cells are the same as mentioned in the macro:

.AutoFilter Field:=3, Criteria1:=Format(.Range("X1"), "d/mmm/yy")

Any other ideas? In the meantime I will be trying some things as well.