Hi,

I'm finding it particularly difficult to explain the issue but I can't get the macro to work in order to automatically update sheets in my Excel Workbook. To fully demonstrate the issue, please kindly see my video here:

https://www.youtube.com/watch?v=SWkJ...ature=youtu.be

Microsoft Excel 2016.

Thanks and Kind Regards,

indieben.

An explanation in words would attract more responses than having to look at a 12 min long video. It would also help you to better understand your own problem

Also post your sheet with a macro and expected results. Thx

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Perhaps using the Worksheet_Change event on column I (Eligibility) to re-run the recorded macro.

Of course that recorded macro would need to be cleaned up to eliminate all the .Select and .Selection stuff by use of variables.

Can't test any suggestions or ideas against a video.
As Pecoflyer says, you'll get better assistance attaching a sample workbook so we have something to work with.

Have you posted this same question to other forums ?

Hi thanks for your replies. I have uploaded the worksheet. I have no idea how to edit macros - I just hit record and stop!

So:

Objective

1. To filter those with an eligibility setting of "registered" into a separate sheet.

2. To filter those with an eligibility setting of "yes" into another separate sheet.

3. In both cases - each should update automatically when I update the data tab.

What I did

4. I created an advanced filter for "Eligibility" and "Yes" and set this up in the new worksheet.

5. I did the same for "Eligibility" and "Registered".

6. I set up a Macro recording me doing the advanced filter after I had tested it manually to ensure that it achieved the desired results. The macro recorded me doing the filters on BOTH sheets - I did not tick "unique records only".


The Problem

7. The filters worked fine manually but the macro created odd results after I had updated the "Data" shet - extra flat/apartment numbers appeared on their own (seemingly matching flat/apartment numbers from complete records i.e. if 23 Smith Street appeared, so did number 2 and number 3.

8. Also, when I changed 10 properties from "yes" to registered - although the 10 records should have disappeared from one sheet and appeared in another after the macro was run, the 10 records disappeared from "Unregistered" but did not appear in "registered" seemingly disappearing in translation.

Ideally, i'm just looking for the other two sheets to auto-update. The video demonstrates the issue best however - there is a fast forward option.

Thanks and Kind Regards,

indieben.

Attached Files

Dummy.xlsm (54.2 KB, 13 views)

See if this does things the way you're wanting.

Attached Files

Dummy_v2.xlsm (57.1 KB, 12 views)

Hi, Yes, so how did you fix it please so that I can add it into the real data spreadsheet? I can see that there is some extra code added - is it simply a case of copying and pasting this? Will it matter if I add extra values to my drop down in the future?

And i'm curious as to what the problem was before please?

Thanks ever so much :-)

before....

1. you changed the values of I4 and I5 which is what you originally copied to the A2's for the filter criteria.
at 10:55 of your video A2 shows "Yes", but it shouldn't because you just change the cell it came from to "Registered".
But if you look at N17:N18 that's where the macro wrote to on the second pass. You didn't specify where to write to so it used the active cell.

2. you didn't remove the old data from the 'two' sheets before over writing it with the new filtered results


now...

Re: 1.) Removed any code that would populate A1 and A2 of the 'two' sheets.
The A1 never changes and the A2 you know what it should be, so just enter it once manually and you'll never need to touch it again.

Re: 2.) First thing that happens is everything beyond row 3 of the 'two' sheets is deleted then the advanced filter is applied.

In a nutshell, the Worksheet_Activate event of both the "Unregistered_Tenants" and "Registered_Tenants" sheets run 2 lines of code and the recorded macro can be removed.


ps: You do realize that hidden row 2 prevents manual filtering of the data, right ?

Hi and thank you so much for taking the time to explain and also for your initial help, i’m terribly hung over so I will give this another read and cross reference against the sheets and the video to try and understand better a little later or tomorrow!

Thanks again and Kind Regards,

indieben.

Hi nosparks,

I've got around to attempting to do a mail merge based on the Unregistered filter - the following is the list of sources available (Screenshot):

https://snag.gy/ajLFSi.jpg

The only "Unregistered" option that has address fields available is the "unregistered extract", however, when I attempt to run the mailmerge, none of the addresses are available and the following error appears:

"Word could not merge the main document with the data source because the data records were empty or no data records matched your query options".

I have hidden the "Eligibility" and "registered"/ "unregistered" fields that appear on each of the filtered sheets.

Any help would be gratefully received.

Thanks ever so much

Kind Regards,

indieben.

Sorry, I've never used mail merge.

Perhaps Administrator macropod will see this and can offer assistance.

Copy the attached document to the same folder as your Dummy.xlsm workbook, then open the document. Pressing Ctrl-A, F9 will cause the two lists in the document to refresh any time updates to the workbook are saved. The lists are generated by DATABASE fields in the document, for which the field code display can be toggled on/off via Alt-F9. For more on DATABASE fields, see: https://support.office.com/en-us/art...9-558a87badcbc

Attached Files

Data Extract.docx (38.4 KB, 9 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi thanks for this, i'm a bit unsure of what you have done here - I will need to implement whatever fix into my real excel files, thanks :-)

If you open the document and press Alt-F9, you will see the DATABASE field codes, the first of which looks like:
Simply replace 'Dummy.xlsm' with the correct file name and save the Word document to the same folder as that file.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]