This is designed to be run on bid proposals, to calculate special discounts. My idea was that whoever was designing the bid would open this file, which has the discount information and the macro, and then run the macro on the file containing the bid.
This macro works. It worked really fast when it was in my personal.xls and referenced an .xlsx file for the vlookup. It was slower, but still tolerable, when I moved it to the .xlsm file and it still refenced the .xlsx file (an oversight on my part). Now that I fixed the vlookup reference to the .xlsm file, the same one that the macro itself is in, it has slowed to a crawl. What is going on?
i have a simple question regarding vba code involving opening/eleboration/closing of different excel workbooks' format. I saw that with xls/xlsx the macro works with relatively high speed, but it is very slow when it comes to xlsm file. Is there an explanation? Does a command which enhance speed for xlsm file exist?
Im currently using excel 2007 and have my workbook saved with the extention .xlsm. I have put a vlookup formula in a cell and have placed $ constant sign se the formula will look in the same column but different rows. However, when I copy and paste the formula down the rows, the formula captures the same data.
I have this formula: =VLOOKUP(E24,OFFSET(Data!E$23,0,0,MATCH(TRUE,Data!E$23:INDEX(Data!E:E,MATCH(9.99999999999999E+307,Data!E:E))="",0)-1,14),14,0) in column e and looking up from a sheet called Data!. This formula looks up off of column e into the data worksheet from the point where I insert a blank row at a chosen point in the data. The problem is that this formula needs to calculate in excel where is shows in the bottom left of the screen...calculating 5%...50% etc. and I have to wait. Does anyone know how to rewrite this formula so it does not have to re-calculate after each move? This formula above requires a CTRL+****+ENTER.
What the formula above does is simply looks up into the other worksheet from column e and looks up only into the data where I insert a blank row. So it creates a lookup range at the point where the blank row is inserted.
The workbook I have attached gives me the exact end results I want, but since most of the formatting was recorded, the code is incredibly long. I'm guessing one of you guys will have a field day, and laugh your **** off, reviewing this code.
My goal is that the buyer take the one master sheet they receive from the supplier, extract the appropriate information needed to two seperate sheets and format them to print neatly. The buyers will use the printed sheets to go into the purchasin program on their computers and make changes to the purchase order. The page set up is so cumbersome because the original worksheet received is never formatted the same, some of these are coming from China, and even the type of paper is set wrong.
I am using the macro below to save a file. It works with the extension .xlsx but not xlsm. I get a message that I can't use this extension for this file type. I am missing something fundamental.
Using Excel 2010 Sub testsave()' Dim a As String Dim b As String Dim c As String Dim d As String b = "Myyfile" c = b & ".xlsm" a = ThisWorkbook.Path d = a & "" & c ActiveWorkbook.SaveAs Filename:=d End Sub
FYI - there are no macros in the file I am trying to save.
I'm on excel 2010 and I have a small group excel files I open everyday. Most of the files are static in name and location. I've got a macro created to open those files, which works fine with workbooks.open and the file path.
There are two report files I want to incorporate into my macro of workbooks to open. The files are created weekly and the files names have the following format: "Report Name (YYYY-MM-DD).xlsm". I don't want to use the file's last modified date because older files may get edited after the more recent ones are created. The files are also not always created on the same day, so the solution needs to be flexible enough to not refer to a specific day of the week or anything.
Macro open an excel file based on the latest date found in filename.
Below is the entire code that I am using. It is a simple routine which checks whether a part has started its release process or not, based upon dates. The code works and does what I want.
The problem that I have is that it is very slow, for example it takes 35 seconds to go through 530 lines items. In my (limeted) experiance, based on other VBA doodlings this slow.
I have the following macro in a worksheet...and it is running very slow. There are other macros in the worksheet and they all run very well. Any ideas by looking at this code why it would be so slow in running?
The code below runs on a spreadsheet that has approx 600 rows which INDEX and MATCH another spreadsheet which has approx 600 rows. I takes about 4 mins to run. Sub QC()
Need some alternative code that would speed the execution of this macro. My weak attempt runs noticeably SLOW.
Dim cell As Range For Each cell In Range("TPD") If IsNumeric(cell) And Not IsEmpty(cell) Then If cell.Value > 0 Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If End If On Error Goto 0 Next End Sub
I programmed a simple macro (attached to a button) that scan the lines in the sheet and uses the function: Cells(y,2).EntireRow.Hide=TRUE (in order to hide certain lines). Usually, It runs fast without problem. But when I print out an area of the sheet, it cause the macro to slow down substantially. (when it runs again). In order to fix that , I had to close the file and reopen it again ( and not print out)
i am currently using the following code to copy records from one sheet to new sheets that that are created and named in the first part of the if(). this works fine, however when i am dealing with 50,000 records it still takes 5-10min to get them all sorted. I think this although functional is horribly inefficiant, and am wondering how i might be able to speed up the process. possibly rather than check each record, then copy then paste individually to sort them, then read and select the list of cells until value changes, then copy over at once. i dont know if this would be faster or not, let me know what you think.
Sub autorec ()
Dim wSheet Dim newSheetName As Variant Dim FNAC As Double Dim OU As Double Dim DS As Double Dim CCY As String
I'm running some macros that take a bit of time (1000-2000ms), and would like to give the user some feedback as to what's happening.... Ideally, I'd like to show a msgbox-like pop-up window saying something "processing..." while the macro executes.
I have tried using userforms (based on this post Excel VBA Macro- How to Create a Splash Screen for your Excel Workbook); however they halt background execution...
Is there any built-in functionality similar to .statusbar, but more obvious/visual that would allow this? I'm about to experiment with showing/hiding a textbox shape, but this seems a bit sloppy...
I have written some very basic code to format a report in excel. When I run the code it take a very long time to execute and I receive the following error message at the foot of the page:
Cell (press esc to cancel)
Annoyingly I have had this error before and found the solution on the web but can't remember where. If memory serves my right I deleted some temp files from a specific location on my hard drive?
I'm working on a macro which compares values in particular cells across different versions of a sheet. Unfortunately there are quite a lot of values, and my code is running very slowly.
I'm trying to get the data in a grid, with the file version across the top, and down the left the project name. For each file version I need to look up a specific value for every project listed.
At the moment, my macro is creating a seperate formula for each cell which does a vlookup on the project name to the specific external file.
The relevant bit of code is below
At the moment its runnig to slow by several orders of magnitude, Working on a 4 by 10 grid it takes about a minute, and I'm going to need it to be able to handle a 50 by 800 grid.
While the code is running, the CPU is not maxed out, so I'm assuming that its the calls to the file system which are taking all the time.
Application.ScreenUpdating = False
Dim ccount As Integer Dim rcount As Integer Dim sFilename As String Dim sPath As String
With ActiveSheet For ccount = 1 To Range("c1").End(xlToRight).Column - 2
My office just converted to 2007 and I am running into a problem with code used to save a workbook. I need to use a dialog box as the file could be placed in several different locations which the user needs to choose.
When the save as dialog box opens it defaults to being saved as the xls type. I would like to modify the following code so the file type drop down defaults to the xlsm type. Below is my code.
my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros() Dim WorkbookRust As String WorkbookRust = ActiveWorkbook.Name ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch" Workbooks.Open Filename:= _ "C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls" Sheets("Main_Overview").Select Windows(WorkbookRust).Activate Application.run ActiveWorkbook.Name & "!UpdateEntries" Application.run ActiveWorkbook.Name & "!FilterMain" 'not ask to overwrite existing file Application.DisplayAlerts = False Workbooks("CH_Revenue_2008.xls").Save Workbooks("CH_Revenue_2008.xls").Close End Sub
I have two workbooks one names "Quote" other named "product supply". In "Quote" i would like to have somthink like a Vblookup that on a givin line can enter price's, disciptions and other data automaticly on that line, From the "Product Supply".
Example:
"Quote" A:1 Dropdown list. Select product, Then refers to "product supply" of that product, then trasphers data into "Quote" A:2
I'm trying to open a .xlsm on an Android tablet with only OfficeSuite installed. Initially, that didn't work. After installing a DocumentsToGo application, it shows me my spreadsheet, but none of the .jpg's that are visible in the original file. I copied all .jpg's to the tablet; both the .xlsm and the folder with the pictures are in the same parent folder, to simulate the original folder structure in Windows. Still the pictures will not show.
I know there are plenty of examples on how to do this at a basic level but I am looking at making it a bit more custom.
I would like to put some variables on a sheet in a workbook and use those to drive the behavior of the code.
EG I would like to specify the save-as-pdf path (eg c:users.etc), the email addresses to be emailed TO (specify a range eg A1:A14 where each cell has a different email addy in it), like wise for CC and then BCC (new ranges), also be able to specify subject of email (EG Cell F1.value is email subject).
I made the excel file with the VBA source code, saved it, opened it many times, and all at sudden I cannot open it anymore. When I try to open the file I get the following screen:
actually, Excel asks me if I want to enable macros or not, but this window is different than usual window:
when opened file with macros works fine.
So, in my case I click the Enable Macros button of the first picture and my file seems to open in some hidden instance, because the file name isn't writen in the title of the Excel window.
When I close the Excel window it asks me if I want to save changes to my file, and that proves that it was opened in some hidden instance of the Excel.
I have to say that I was not playing with the security options of hiding any part of the file that I want to open, and which doesn't open properly.
I'm creating a template xlsm file that has built in marcos. This file will be used by the Engineer as a template, where they fill in the data, and then send it to the team. when the case in finished, they will use clear data macro.
The Problem I want the template to have a macro runs with startup of the file that checks for the lastest template in a network folder (shared folder where they will have read only access). If the used file is the latest one, OK. If not, give a custom msg with the location on the network.
