Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 07-12-2018, 12:48 PM
indieben indieben is offline Windows 10 Office 2016
Novice
 
Join Date: Jul 2018
Posts: 6
indieben is on a distinguished road
Question Automating Updates For Advanced Filtering Into New Sheet

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.
Reply With Quote
  #2  
Old 07-12-2018, 11:58 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,191
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

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
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 07-13-2018, 07:33 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 638
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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 ?
Reply With Quote
  #4  
Old 07-13-2018, 10:39 AM
indieben indieben is offline Windows 10 Office 2016
Novice
 
Join Date: Jul 2018
Posts: 6
indieben is on a distinguished road
Arrow Further Information

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
File Type: xlsm Dummy.xlsm (54.2 KB, 4 views)
Reply With Quote
  #5  
Old 07-13-2018, 04:56 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 638
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

See if this does things the way you're wanting.
Attached Files
File Type: xlsm Dummy_v2.xlsm (57.1 KB, 2 views)
Reply With Quote
  #6  
Old 07-13-2018, 06:59 PM
indieben indieben is offline Windows 10 Office 2016
Novice
 
Join Date: Jul 2018
Posts: 6
indieben is on a distinguished road
Default

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 :-)
Reply With Quote
  #7  
Old 07-13-2018, 10:29 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 638
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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 ?

Last edited by NoSparks; 07-13-2018 at 10:40 PM. Reason: added the ps
Reply With Quote
  #8  
Old 07-14-2018, 04:45 AM
indieben indieben is offline Windows 10 Office 2016
Novice
 
Join Date: Jul 2018
Posts: 6
indieben is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
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.
Reply With Quote
  #9  
Old 07-19-2018, 01:27 PM
indieben indieben is offline Windows 10 Office 2016
Novice
 
Join Date: Jul 2018
Posts: 6
indieben is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 07-19-2018, 05:07 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 638
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Sorry, I've never used mail merge.

Perhaps Administrator macropod will see this and can offer assistance.
Reply With Quote
  #11  
Old 07-19-2018, 05:58 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,603
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
File Type: docx Data Extract.docx (38.4 KB, 1 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #12  
Old 07-23-2018, 01:54 PM
indieben indieben is offline Windows 10 Office 2016
Novice
 
Join Date: Jul 2018
Posts: 6
indieben is on a distinguished road
Default

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 :-)
Reply With Quote
  #13  
Old 07-23-2018, 04:08 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,603
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

If you open the document and press Alt-F9, you will see the DATABASE field codes, the first of which looks like:
Code:
{DATABASE \d "{FILENAME \p}/../Dummy.xlsm" \s "SELECT `Flat/Door Number`, [Address First Line]&''&[Address Second Line] &''&[District]&', '&[City]&' '&[Postcode] As `Address, Locality & Postcode`, `URC`, `Ref Code`, `Eligibility` FROM [Data$] WHERE [Eligibility] = 'Registered' " \b 58 \l 9 \h }
Simply replace 'Dummy.xlsm' with the correct file name and save the Word document to the same folder as that file.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Tags
advanced filters, excel 2016, macro

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding Capital Letters and filtering them out in an excel sheet ch1325 Excel 4 08-20-2015 07:25 AM
Filtering (advanced?) in Pivot Table canajun Excel 6 01-17-2015 05:01 AM
Need help in automating data from multiple sheets into a single sheet veedee Excel Programming 8 06-12-2014 10:04 PM
Need help in automating data from multiple sheets into a single sheet veedee Excel 1 06-11-2014 04:01 AM
Automating document updates with new text wordfoolish2 Word 0 01-04-2011 01:01 PM


All times are GMT -7. The time now is 02:19 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft