I need to create a macro that will auto filter rows by column A and for each filter save a PDF that is titled by whatever is filtered in Column A.
A, B, C
Rob, 2, 4
Nick, 1, 3
John, 10, 20
Rob, 50, 100
So first filter would create:
A, B, C
Rob, 2, 4
Rob, 50, 100
and save as PDF titled "Rob"
Writing code to do the following:
Save advanced filter settings
Remove filter (or simply set to be 'select all'
Run other code (I have this piece of course)
Put filter back on with same selections chosen as when it was removed
(Need this becuase the code in the middle does not work properly when the data is filtered)
Is there a simple way to loop through and filter the values in col1, then save it to a file named by the filtered value? Would be good if it could keep all the formatting as well.
View 1 Replies View RelatedHow can I FILTER a range and display the unique items, one below the other, WITHOUT blank cells - with only a FORMULA. What I came up with is shown in the attached WB. I would like to present the countries like in C11:C15.
View 5 Replies View RelatedI have a database in Excel 2013 and now I want that when a value (a person's name) is entered in a cell. That then the database sort of filters the list for me, so it's still possible to make changes in the entries.
[URL]
Picture above to specify the search, which I would therefore like to edit
Dashboard_Action Pool Team 7.2.xlsm
I have been all morning working on a simplified version of the tutorial from YouTube: Create your own Excel Search Pt. 4. But came back later so only then that I can not change the data:?
I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
[Code] .....
Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...
How to automatically filter can filter and blue-colored cells.
View 4 Replies View RelatedI just got into the world of PowerPivot, Excel 2013 and Pivot Tables and am in the process of creating a Dashboard which I will then be uploading to SharePoint 2013.
On top of page I added the new timeline filter which I've linked to my pivot charts. Now what I would like to do, is create an additional pivot chart which looks at whatever date range has been used in the timeline filter and subtract 5 years from that. So, when I select a date range of November 2013 - December 2013 in timeline filter, the additional pivot chart will show the details for November 2008 - December 2013. This is where I get stuck.
I'm using two SSAS cubes which I'm combining together in PowerPivot and then display in Pivot Tables and Pivot Charts.
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
I have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.
View 4 Replies View RelatedWhat I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.
All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.
I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .
The macro itself is saved in personal.xlsx.
I would like to add some icons on the left side of excel open file pane to faciliate my work. Because i need to load some files under the same folder many times a day. Does anybody know how to do that? I've seen people has more icons on the pane before. The defaut setting has only 'History', 'My Documents', 'Favorites', 'Desktop' and ' My nutwork places' on it.
View 2 Replies View RelatedCan anyone explain why the unique filter does not produce a unique result - sample attached?
View 2 Replies View RelatedI m trying to use an Autofilter to filter my cells with a Number Filter of is greater of equal to 4 and is less than or equal to 5.
But as you can see I would like to customise is using a range of 2 values which i have specified in Cell P1 and Q1.
I manage to figure out how to reference to this cell, but Im not sure how can i put my ">=" and "<=" operators into my code so i can get it to work exactly how i want as shown in Code 1.
Code 1
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$118").AutoFilter Field:=6, Criteria1:=">=4", _
Operator:=xlAnd, Criteria2:="<=5"
Code 2
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$118").AutoFilter Field:=6, Criteria1:=Range("P1").Value, _Operator:=xlAnd, Criteria2:=Range("Q1").Value
I have some columns on which I have a filter, with some columns next to those that have information in them.
What I need to do is filter only columns A-F when apply filter values, but keep columns I-K fixed as A-F change when they are filtered..
I've a table of data which users auto-filter. I'm wanting to capture the filter criteria each time the filter is applied / re-applied. Can't seem to find an 'event' for the auto-filter.
View 4 Replies View RelatedHow do I go about using an advanced filter to filter a list of data e.g.
boat
boat
boat
car
car
truck
and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with
boat
boat
boat
1- Force cell format date to by (yyyy/mm/dd) only, with worng msgbox( validation).
2- Make the first day of a month in a color cell
I've Tried this In Conditional Formating (=VALUE(right(A1;2))=1) but didn't work
3-Make Advanced Filter to filter data between two dates .
I am in the process of making a database more efficient and am running into a problem with sorting data. I currently am trying to use AutoFilter to sort the data. When I want to then narrow the results further using the same column as the critical and there is nothing that matches the critical, I get everything from the entire database that matches that critical rather than what I want to see, which, in this case, would be nothing.
I have attached a sample file. In it, when All AF 1000 is run and then Selected MAC 2000 Wash is run, I want to see no results instead of seeing all MAC 2000 Washes from the original data set. I can do it using IF/THEN but I am looking for a faster way to do it.
I have a UserForm in which i have inserted a CommandButton. I also have a Table on a Sheet in Excel that i would like to save as PDF.
When i click the CommandButton on the UserForm i would like the SaveAs screen to come up and have the Table (or Used Range in the Excel Sheet) as the selection to be Saved as .PDF format.
Each of the worksheets in my model use A1 as a control cell for any errors and inconsistencies. My aim is to disable save and close commands in case A1 is not equal to 0 in any of the worksheets.
The code I currently use for that purpose is as follows.
I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.
View 6 Replies View RelatedI have a macro that copy one sheet of the Active workbook and sends it via email.
I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.
I have a sheet which I would like to back up online every time it is saved.
I have a sub, OnlineFile(), which I have used many times without any problems, which creates a .BAT file to use ftp to upload files onto our server. As arguments, it takes the local file path that you want to upload (or download to), the name of the file as you would like it stored, the online file path, and whether you want to upload the file onto the server, or download it off the server.
It should be very easy to combine the two: what I tried was:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "" & "Rubbish.xls"
Call OnlineFile(ThisWorkbook.Path & "" & "Rubbish.xls", "Rubbish.xls", "wwwrootexcelfilesMasters", "Upload")
Kill ThisWorkbook.Path & "" & "Rubbish.xls"
End Sub
Every time I ran this, the file got uploaded correctly, but Excel had a series of problems. Often the code ran fine the first time it was run, but crashed the second time round.
After a fair bit of troubleshooting, I found that the problem came about when the file was trying to save itself - i.e. after the end of the sub. If you pause any macros from running, it still saved fine, but when the code ran, it found errors, froze or closed excel.
My thought at this point was that the code was fragmented or something. I made a new sheet, and retyped just the relevant bits of code (i.e. the BeforeSave sub, the OnlineFile sub and created the form called by OnlineFile). The problem persisted.
I've come up with a workaround for this now (which uses the same OnlineFile sub & form, and works fine), but it's driving me crazy - why does this cause excel to die? Particularly on the second time through? The code seems far too simple to cause such problems - is there a bug in Excel perhaps (I'm using Excel 2000 on Windows XP).
How do I filter multiple columns of information with the use of one dropdown arrow?
see the attached picture (jpg.) for a visual representation of exactly what I am trying to achieve. Excel Filtering.jpg
I have also attached an Excel Filtering Example spreadsheet for use in explaining how to create this type of stacking filter. Excel Filtering Example.xlsx
I am trying to disable the save and save as function using a macro, i know that this in its self is quite simple but i am making VBA create a new workbook and i want this (nameless) workbook to have the save feature disabled.
View 1 Replies View Relatedwhen i try to save data in other sheet. it is save but, if middle name is null in last record. then after i save other record the data is not save in same row.
i also want "sr.no" come automatically form data..
for.ex.
if in sheet2 last "sr.no" is "7" then it automatically display "sr.no" "8" in sheet1
I have a spreadsheet with 32 worksheet, I want to write a macro to "save the work, save as.. in a specific location and with a specific name and then reseting all data in specific cells.
View 14 Replies View RelatedI need my spreadsheet to require that the user save it by clicking a macro button, and not the Save or SaveAs options in the file menu. I have found out how to disable saving entirely:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
If SaveAsUI Then SaveAsUI = False
End Sub
But this of course prevents my macro button from saving too!
Is there any way to allow VBA code to save the spreadsheet while simultaneously preventing the user from saving through the menu?
