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..
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" ? ...
Is there anyway to change the security settings in Excel 2007 using VB macros? I have tried to record the macro as I change the settings but nothings is recorded. Basically I have two workbooks, and I want to run one of the macros within workbook 1 from workbook 2. This is straight forward, however why I try to run it, excel says that the macros are disabled in the other workbook, but I know they are not, they are all enabled (bottom option)
I was hoping to find some way of fooling Excel into thinking the macros were enabled by changing the security settings (actually keeping them the same, but making excel think that i've changed them) before I try to the macro from the other sheet. I don't know if i've explained myself very well here, I hope I have, if not let me know and i'll try to go into more detail.
I have a range of cells ( C2, C5, C8:N1007, P8:P1007 ....) on a worksheet that I want to be able to clear the contents of through a macro - worksheet is entitled Database
I have 3 additional worksheets all of which have summary information on and some of the formulas are huge.
I've inserted a module and produced this code;
Sub ClearContents() Application.Calculation = xlCalculationManual Range("C2, C5, C8:N1007, P8:P1007, ....").ClearContents Application.Calculation = xlCalculationAutomatic End Sub
Because of all the 3 summary page formulas the code is taking a few seconds to run.
I'm new to VBA and just wondered whether I could switch the calculation setting for the entire workbook onto manual at the beginning of the code and then switch it back to automatic at the end of the code? Think this might speed things up.
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.
I have been using this code and just noticed that it resets all of my options buttons to false when I exit and then re-enter (activate) the sheet with the option buttons.
I see where this is going on, but don't know how to correct it. I only want the option buttons changed to false if
The Sol named range is something other than "Primary Vendor". It seems to call the macro ClearOB whenener the sheet is activated.
Private Sub Worksheet_Activate() If Range("Sol").Value = "Primary Vendor" Then For Each OB In ActiveSheet.OptionButtons OB.Enabled = True Next OB ClearOB ActiveSheet.ScrollArea = "A1:K58"
I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.
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.
Second, all the rest of us can click the print button to print the one single page that we are viewing. He says when he does this, that each time it automatically starts printing all the pages in the workbook. He wants to only print the one page.
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.
At my company we need to print our workbooks containing 3 to 24 worksheets. The first page should be printed on different paper than the other pages. From various forums I gathered that it's not possible to set the papertray using vba. Those forums suggest 3 things: 1. Windows Api functions (don't work at our company(security)), 2. SendKeys (don't work because of different printers, office versions and future-proofness) and 3.
Define printerdrivers per tray.
This last I did, so there are printerdrivers for tray3 and tray4.
With the following code I try to print the workbook:
Public Sub printSheets(strP1 As String, strP2 As String) Dim curPrinter As String, firstPage As Worksheet, otherSheet As Worksheet, x As Integer Set firstPage = Application. ActiveWorkbook.Sheets(1) curPrinter = Application.ActivePrinter
The problem: The sheets are sent to the printers specified as strP1 and strP2, but the tray-settings from these printers are "overruled" by Excel; they are printed from the papertray that was the default of the printer when Excel was started.
Also, using the default printer dialog from Excel has the same problem; when the (windows-)default printer is Tray3 then all the pages come from there, even when printer Tray4 is selected as the printer. In Word or Acrobat etc the prints come from the right tray.
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'm attempting to develop an application that will get world-wide usage, but my first tester is in the UK (Danish) and the app Kacked on a simple time format command.
Since this is intended as a self contained project, is it possible to establish those settings as part of my project?
I currently have a template that I have made and all the info and everything is fine on it, but the problem is with printing. Every time I go to print it wants to print out 100 copies of the same thing. I tried editing the original template by printing 1 copy then saving the template, but it still wants to print 100 copies with the next try. how to customize what the print settings are for the template and make them stay permanent with the template?
I am trying to copy the print settings(including the headers and footers) for one excel worksheet to another. I have found the following hint on internet and it works fine for copying the print setting(including the head/foot) but it is also grouping the worksheets. Click on the tab of the worksheet you want to copy. Then hold down the Shift or Ctrl key and click on the tab of the worksheet where you want the settings and click on File, Page Setup and OK. How can I just copy the print setting without grouping the worksheets?
I am working on lots of excels, with data queries from sql and pivot tables. Is there a way to set defaults to some settings in excel (not in this specific file).
To be more specific:
1. Import data from SQL: Refresh on opening, save password 2. Formatting of number to have two digits and a comma separator 3. Pivot table: when adding to values sum instead of count, refresh when opening the file
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.
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 try to change the chart position (chart properties) to make it auto- move to empty cells, but anyway i fail.
say example:- i have a excel sheet,
Worksheet 1 ------------ raw data, all records.
worksheet 2 ------------- 1 pivot table + a graph/chart at the same sheet.
top area of worksheet is pivot table, below is chart. The pivot table size can be change from time to time (by increasing the records at the worksheet 1)
settings or using vba to control it? by auto move the chart position down, and not overlap with the pivot table.
I have a battery sizing workbook, which has various tables and active cell VBA routines, that enable the user to input a number and then select a cell from the table presented on the front worksheet. This will then display the solution including other data. I have now been requested to share the workbook and all I seem to do is lock it up and therefore stop and calculations. Therefore your assistance is required in two area's:-
1. How to protect the workbook and allow the user to select a cell on the front worksheet, without all the all the VBA routines appearing to grind to a halt! I can lock an hide all the subsequent worksheets without freezing the front worksheet. 2. I can hide all the toolbars, row and column headings under my Tools/Options which will display the worksheet neatly...... Therefore can I some how instruct the users Excel to turn these off when loading?
How can I change the default settings I get with a new Excel document?
What I want is that when I create a new Excel document, all cells should have Comic Sans MS font, Bold & size 10, with a particular horizontal and vertical alignment etc.
I am not able to do these changes using settings in Option menu.
