VBA - Data not transferring when running macro

[aeronaught]

Hi guys,

I'm currently running the following:

Please Login or Register  to view this content.

However, the data isn't transferring to the new workbook and I can't quite figure out why.

Any help is greatly appreciated!

[JLGWhiz]

Please Login or Register  to view this content.

Using the inputbox makes the variable a string. Use the CDate function to convert the string to a date data type.

[aeronaught]

Unfortunately, it's still not working.

I've ran debug, and when it gets to this part of the code:

Please Login or Register  to view this content.

It applies the filter, but no data is selected for transfer between the date range specified.

Any ideas?

[JLGWhiz]

That is what the CDate is for. It is not finding the value of your variable from the input box. I had the same problem when I ran a test set up. Then I used the CDate on that line of code As shown in Post #2 and it then filtered the data correctly and copied it over. Also, you are essentially filtering on the UsedRange.Offset(3) with column 15 ("O") being the key search range for the criteria to match. Make sure you have the correct column with dates in it.

[aeronaught]

Still no luck.

As far as I'm aware, everything is in the correct column.

I've attached a mock spreadsheet with all sensitive data redacted. Can you see where I'm perhaps going wrong?

[JLGWhiz]

It could be your system default date format is the problem because when I tried runing the code on my system it automatically converted the input box dates but not the dates in column O and that resulted in the filter failing. Dates are a real pain to work with sometimes.

[Fluff13]

Try

Please Login or Register  to view this content.

[aeronaught]

Try

Please Login or Register  to view this content.

Well blow me down. It worked! Thank you my man!

[Fluff13]

Glad we could help & thanks for the feedback

[JLGWhiz]

That's the trick with dates. When trying to match between different cells, they have to equate to the same value. Date data types are either long integers or doubles if time is included. But dates can also be entered as string values, so when trying to match between cells, you have to determine what each cell value equates to in terms of data types.

[aeronaught]

Really appreciate your help you two! I've been at a loss for days. I can't believe it was something so simple!

Just to piggyback off of this (is that even allowed? I can't find anything in the forum rules) - at the moment, I have to open a separate spreadsheet named "End of year" with exactly the same tab names as "FO New" for this macro to work. Is there any way to have the macro automatically generate a spreadsheet called "End of year" with all the tabs and information I'm after?

[JLGWhiz]

Please Login or Register  to view this content.

You could use something like this which would save the existing workbook with the sheet names you want, using the workbook name you want. Then the loop will take the existing data out of the sheets, leaving bland sheets with original names, except any macros will be available if the original file was macro enabled. But all data will be gone.