I am trying to figure out how to save a worksheet's autofiltering settings and be able to re-create those settings.
I would start off with a spreadsheet with a few Autofilters on. I would like to make a macro that will record the initial Autofilter settings, then the macro would do a bunch of stuff that would need to 'Show All'. After these actions take place, I would like to be able to return to the initial Autofilter settings. I'm not even sure which VBA command should be used for this sort of thing.
Undo / Redo - they do NOT work properly after a save. Before a save, its great - I can undo / redo many many changes.
After a save, I can only undo 2 changes, but the worst thing is it ONLY REDOES ONE of them.
I work by saving very regularly, then I go and look at something on the sheet, and then use Undo / Redo to get back to where I was last updating the sheet and see what I had just been working on, especially if its a few minutes in between.
However, when I do that now, it undoes my last change, but the redo has been lost. So I do:
change, save, undo, redo
and it just doesn't do the redo. The redo button is greyed out.
If I do:
change, change, save, undo, undo, redo, redo
the first redo works, and the second fails as the redo has been lost.
It seems pretty basic that undo / redo should be symmetrical but they aren't, at least now.
I would rather just not have undo after save at all than this broken version.
Why are they putting so much stuff in Excel but they can't even get one of the most basic parts of spreadsheets correct - such as undo / redo, and the ability to open independent spreadsheets without having to change things in the registry (which I have had to do so I can work on two spreadsheets in a semi-sane fashion, undo being shared across your sheets otherwise). MS needs a back to basics...
Is there a way to force a print RANGE, and add settings such as margins, and print to range to 1 page wide and 1 page tall? Can this be set with VBA? I tried to make a marcro but it crashes. This is for Excel 2002.
Is there way we can save the layout and other preferences / settings (and there are a huge no of those in excel) to a file or can we copy out the file where excel saves this data ? Probably a User Interface folder or something ? This is so that when i do a fresh reinstall of MS OFFICE i dont want to spend so much time resetting the customizations ive created over time.
I was reinstalling office on a laptop where the hard drive had been replaced. I used the 'save settings, wizard on my desktop and put in the new settings when I reinstalled Office.
This almost worked, but not quite.
Word and PowerPoint seem to be fine but when I reopened Excel I got a number of messages saying that objects could not be loaded because they were not on the machine. Some of this was because Excel was trying to reference add-ins that needed to be reinstalled. I have done that, and there are now fewer messages - but there are still two and I can't get rid of them. One looks for 'objects' the other a single 'object'. There seems no way of finding out what the objects are in question, and what it that Excel is stumbling over. Can I do anything other than abandoning my previous settings and starting again, or else living with having to click twice every time I start excel to get rid of the messages?
I have tried uninstalling Excel and reinstalling but this doesn't help. Perhaps registry changes are the problem...and I hate the registry.
I am trying to use a shell command to dig into a specified directory (and ideally all sub-directories) and open every pdf file and re-save with security settings changed (adding a password to prevent printing). I can't seem to get the shell command to run from where it is:
Option Explicit
Sub OpenPDFFilesAndSave() 'opens every pdf file in a directory Dim FSO As Object, Fld As Object, Fil As Object Dim MainFolderName As String, i As Integer Dim RetVal As Variant
Set FSO = CreateObject("Scripting.FileSystemObject") MainFolderName = "C:Test" Set Fld = FSO.GetFolder(MainFolderName) For Each Fil In Fld.Files i = i + 1 RetVal = Shell("C:Program FilesAdobeAcrobat 7.0 _&AcrobatAcrobat.exe MainFolderName & " " & Fil. Name") Next 'have to add save the file, and change the print settings but not figured that out yet End Sub
I am having trouble getting Excel 2007 on my work computer to save as .xlsx by default.
I have opened the Office Button > Excel Options > Save > Save files in this format and selected "Excel Workbook (*.xlsx)," and if I save as before closing Excel then it works perfect and saves the file as .xlsx. However if I close Excel, re-open it later and save a new file the default "Save as type:" is "Excel 97-2003 Workbook (*.xls)" and if I open the save options again the "Save files in this format" option is reverted back to "Excel 97-2003 Workbook (*.xls)."
I have finally overcome the [Compatibility Mode] issue by saving a file named "Book.xltx" (not "Book1.xltx") in the two default locations "C:Program FilesMicrosoft OfficeOffice12XLSTART" and "D:Documents and SettingsusernameApplication DataMicrosoftExcelXLSTART" (we use the D: drive at work for personal files). I thought this would solve the save as issue but it hasn't. I have also changed the "Save files in this format" before saving the .xltx files in the locations to apply the settings to those specific files but that hasn't worked.
It is on my work computer so I am limited in what settings I can change because they have them pretty well locked down.
The following line of VBA code executes properly if machine settings are US English, but throws a "Run-time error '1004': Application-defined or object-defined error" ? ...
Excel 2007 - need to adjust protected view settings but the protected view tab is missing from my Trust Centre settings - have the following tabs - trusted publisher / location, add-ins, activeX, macro, message bar, external content and privacy options. Document is a revenue authority download and without being able to adjust the protected view settings, can't input data.
Couple of the end users are unable to use undo/redo functions on certain excel worksheet. I have uninstalled/reinstalled office 2003 without success. Then I even installed office 2000 and office XP without any luck. Have checked into macro's and clipboard and also got idea of adding registry value.
i implement DTPicker inside a userform in my pc ... but when i open the userform in other pc there's an error saying could not found DTPicker ... when i reopen it in the pc that i've made the userform the DTPicker is no longer there and i had to redo the same thing over and over again when others opens it in other pc.
I have a worksheet that is password protected. The userforms used to input all the data have an initialize event that unprotects the sheet to input the data and then before protect the sheet upon closing the userform. This works perfect so that input data cannot be changed once entered; however, the problem I have is that I need to allow autofiltering so users can sort through their input data. I have a command button that unprotects the sheet and places the autofilters in the correct locations, but once this is activated the worksheet then is open for modifications by the users. Is there a way that I can allow autofiltering while still keeping the data protected? If .
I am trying to make it so a user can input a variable such as a date and then press a button which will autofilter a lot of data by what the user entered. The problem is some of the data I want users to be able to filter by needs to be filtered using "begins with" or is "greater than" or "less than". I can only make it so I can filter by "equals" when I try to do for example "begins with" using "*" it doesn't work out. Here is an example of my code
This code will auto filter data by what ever is entered into B4 on sheet 1. I can't figure out how I can filter with the "begins with" function or "less than" function using a variable.
Some of my Excel files work perfectly in terms of undo/redo, while others simply cannot use that feature. I took a stroll through the internet looking for the answer, but the issues that I found seemed to point to the application losing its ability, which was resolved with a registry edit/update/change. I don't think that's the case here because I CAN undo is SOME documents.
After some investigation with my problem documents I have found that THIS Macro is the one thing all my undo-free documents have in common: [URL]
I have a spreadsheet with approx 12000 rows.8 columns. it represent material in our inventory and each row is a specific records that contains a lot of info. I need to be able to have a field in a top pane (could be top row i guess. or a small form) where i can start typing text and as i am typing, the spreadsheet auto filter the rows to display only whatever fit my parameters i am typing. a few example of what i will type in are:
bearing*2217 valve*gate*3*150 asco*8344
as you may have guess, "*" is a wildcard. The spreadhseet are generated using crystal reports over night, so i am not sure how i can or should use macros or add in for this..
When I protect my sheet and have the option to allow the users to autofilter the sheet, how come it doesn't work? I want to allow people to autofilter the data in the worksheet but not modify the contents of the cell.
I have a large spreadsheet which has a column of dates (dd-mmm-yyyy) in it. I would like to be able to filter by month regardless of dd and yyyy. I would like to see all quotes in Jun.
I am able to autofilter a sheet to show only rows with a date in Column U but no "x" in Column BC. Now I want to be able to put an "x" in BC in only the filtered rows. The purpose is if someone enters a date in Column U, the macro will show only those rows minus the rows that have already had an "x" added to BC.
For the most part I've gotten this to work using but it will also put an "x" in Column BC, row 3001 on down.
How can I get it to stop at the last row?
Cells.AutoFilter Field:=21, Criteria1:="<>" Cells.AutoFilter Field:=55, Criteria1:="=" Columns(55).SpecialCells(xlCellTypeVisible).Value = "X"
I need to delete entire row of the result of autofiltering i.e. the visible rows only
If I do this manualy it works but if I record this on a macro, it records the absolute cells range I pick, when in fact the result of the filtering is different every time.
Is there a way around this with VBA?
I've Seen Copy paste in this forum but in my case i need to delete the entire row.
I have a spreadsheet which I distribute weekly to certain people. It is basically a top level view of incidents, which shows the following.
Sheet Number 1 is : Person - Count Joe Bloggs - 42 Fred Bloggs - 12
And so on.
This shows me a summary of how many incidents per person have been logged.
Sheet Number 2 is the actual raw data for that count, so it shows every line. So in the above example, there would be 42 lines for Joe and 12 for Fred.
What I'd like is to put a hyperlink on sheet 1, whereby if you click onto Fred's name, it will take you to sheet 2 and autofilter to only show Fred's records (i.e 12 of them)
I created a lookup table that works quite well. It even has if statements in the LookUp Formula. However, I have to update the table it pulls the information from each day. I wind up recreating the range each time because the table always has more rows each time. Is there a way I could just paste the table in each day and not have to change the range? The columns never change.
how do i change the default settings in excel, for example when i open excel it shows numbers on both rows and columns and i want it to show letters on the colunms. i know how to change the r1c1 reference style but how do i get it to stay the way i want?
I have developed a process design application in Excel using User Forms to input data. The data are placed on a sheet of a workbook with the majority of the calculations being done by cell-to-cell calculations so that users can view the formulas if they so desire. This application works well in English. We want to use the same application in our German office where it has not worked well.
In order to trouble shoot the problems, I can go to the Region and Language setting under Control Panel and set it to German (Germany). This converts the number format to a period for the thousands separator and a comma for the decimal. This seems to create a lot of problems for VBA that I have not been able to sort out.
To simplify the problem I have written a very simple program. It has one User Form with two text boxes for number input, an Enter command button and a Close command button. Sheet 1 has a command button that opens the User Form. In the User Form, I enter a number in both text boxes and click Enter. VBA code then enters the number from Text Box 1 in Cell A1 and the number from Text Box 2 in Cell A2. Cell A3 has an equation that calculates the sum of A1 and A2. All cells are formatted as General.
The workbook works as expected in English. When I set my PC to German (Germany) the application works as expected when I enter an integer in the text boxes. However, if I enter a decimal such as, in the German format, 10,5 and 5,5, the numbers entered into the spreadsheet are text (left justified) and the sum is zero. I have to enter 10.5 and 5.5 to get the numbers to enter as actual numbers and give the correct sum.
I have a large file of data and the data looks like this repetitively, however, there are also useless data. But I figure out that the repeat data looks the same compare to the useless data. I need to extract the data that I only need. Is there a way to set a macro to search for a String from the beginning of the excel file, when the string is found, it will keep the 2 rows above and 56 row below it, then loop.
Here is an example of it:
A1 USELESS DATA A2 USELESS DATA A3 USELESS DATA A4 USELESS DATA A5 GOOD DATA
[Code] ....
The macro will start from A1 then go down till find STRING, then will keep 2 row above it which is A5+56 row below it which is A63.
Then continue at A64, then find STRING at A66 and do the same which is copy A64 (2 row above)+till A122 (which is 56 row below), then continue.
Since A123 to A155 does have any STRING, they will all be deleted..
