i have a workbook that is only 345kb in size it takes ages to open and although it has lookups and retrieves data from the web it just seems like it shouldnt be so slow
i also keep getting a message at the bottom like this:-
calculating (1 processor ) thena %
I have a large Excel 2007 file, around 60.000KB. 54.000KB are due to one of the worksheets where I have 8760rows x 160columns with data. The calculation time is not a problem, it is very fast, it only takes 2/3 seconds. The problem is when I open or save the file, it takes around 2 minutes... it is not too much, but it becomes too long when one has to open and save it several times. It there any trick to decrease the time when openning or saving an excel file??
View 3 Replies View RelatedNeed to have a particular workbook open at a specific time...
I wrote this and placed it in a Personal.xls module, did not run automatically. I then placed the 2nd SUB() in the "This Workbook" and nothing. Where or how is the proper way to run this...
----------------------------
Sub Open_IndexAnalysis()
Workbooks.Open Filename:="e:Index Analysis.xls"
End Sub
------------------------------
Sub Run_OpenIndexAnalysis()
Application.OnTime TimeValue("09:40:00"), "Open_IndexAnalysis"
End Sub
-----------------------
i get the following error everytime i open my workbook
Removed Records: Object from /xl/printerSettings/printerSettings2.bin part (Print options)
Removed Records: Object from /xl/printerSettings/printerSettings3.bin part (Print options)
ive tried everything. ive attached the workbook (obviously with all data removed).
I have recorded 7 different macros and then combined them all into one macro to achieve one end result. I am not sure if you can just look at the codes to determine different ways to improve them or if you need the excel spreadsheet as well.
View 2 Replies View RelatedAny code I can stick into the end (plus maybe start) of a macro to display the length of time it took for the macro to run?
View 9 Replies View RelatedI've done quite a bit of searching in the forum and online and haven't found anything that's generic and can be used at anytime.
What I'm looking for is a way or for code that tells you how long it takes a macro to run from start to finish, something that can be used to time any macro. I've seen some threads in the forum where people indicate that it took x amount of seconds for their macro to run but not sure how to do it.
[URL]
Excel takes about 10 minutes in the saving process. When I say 10 minutes, I mean, the excel screen freezes (says not responding) for about 10 minutes, then it actually saves at the very end in the normal time any other file would take as you watch the progress bar go forward.
I know many of the common answers and have tried. reducing the calculation time (which in turn reduces the saving time).
But in my circumstance, the calculation takes a very reasonable amount of time, and you see the progress % going forward.
- I would say I have about 2000 rows, and 15 columns.
- They have sumifs formulas.
- They link to a different workbook.
- The workbook I am working on saves to the network
- the source of my sumifs are also in the same folder on the network
- the recalculation takes about 10 seconds at most
- i have turned off recalculate before saving, it is all on manual calcs
- when i hit save, there are no calcs being performed
- there are no macros in the workbook
- there are only about 2 names in the name manager
- then it freezes for about 10 minutes.
- then the progress bar starts moving then it saves.
What is it doing in those 10 minutes?
1 more item to note, when I break the links to the workbook and thereby removing the sumifs formulas, its a snap.
Why does the existence of the sumifs extend saving time? I would completely understand if it elongated calculation time, but if calculation is off, then why does it even worry about it when saving?
I am using the below formula to distinctly count the number of customers that match the criteria that I have in Cells C7 and B10. The data is in a separate worksheet, that I am showing Named Detail of which will be changing on a monthly basis, so a pivot table does not want to be used. The detail data ranges from row 7-40,000, and the file is currently 8610KB's, and can potentially grow.
=SUM(IF(FREQUENCY(IF(Detail!$A$7:$A$40000=C$7,IF(Detail!$B$7:$B$40000=$B10,IF(Detail!$D$7:$D$40000<> "",MATCH(Detail!$D$7:$D$40000,Detail!$D$7:$D$40000,0)))),ROW(Cust)-MIN(ROW(Detail!$D$7:$D$40000))+1),1))
This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple column and row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2003.
I have the following code below for a timer in a userform. Right now it is configured to start when a button is clicked. I would like to have it automatically start the timer after the workbook is open for more than 5 minutes but so far have been unable to get it to do so. I have tried putting the code in the workbook module but it still won't run. It also has a button to reset the timer if they need more time in the workbook and also a button for them to save and close the workbook if they are finished. I'm running Excel 2007.
VB:
Private Sub CBReset_Click()
Dim T, E, M As Double, S As Double
T = Timer
Do
E = CDbl(Time) * 24 * 60 * 60 - T 'elapsed time in secs
M = AllowedTime - 1 - Int(E / 60)
S = 59 - Round((E / 60 - Int(E / 60)) * 60, 0)
[Code] .....
Is there a way to have a macro run every time a sheet in a workbook is accessed? Something similar to the workbook open event, but for worksheets.
View 2 Replies View RelatedI'm a fan of the Excel 2010 table styles, but can't figure one thing out. When I create a table in excel (Ctrl+T), I like to reformat it with a new defined style. When I save the style, I can use it while I'm in that instance of excel, but when I close and reopen excel, the style is gone. How to save the custom formats to they are always available?
View 3 Replies View RelatedI have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook
open macro-
Private Sub Workbook_Open()
Dim dTime As Date
dTime = Time
If dTime >= TimeValue("9:30 PM") And _
dTime < TimeValue("9:40 PM") Then
ImportData
End If
End Sub
This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
I have a presentation that I open from a short-cut. After the "Welcome Page" is opened, I want to open a second workbook in a new instance of Excel after 4 seconds.
I think that I can open the new instance of excel, but I don't know how to activate the macro after 4 seconds.
I'm sure there is a function someplace for this that can be used in a macro.
Then, after the second Workbook is opened, I want the Welcome Page "Workbook" closed, leaving the second Worbook open.
My Splash screen opens a few seconds after the workbook has loaded. Is there a way to make the workbook open minimzed until the the splash screen closes then open properly? What I trying to say is that only the splash screen is visible until it closes.
View 5 Replies View RelatedI would like to write a Sub that will see if a workbook is open and if it is not then open it. I know how to have a macro automatically open a workbook, but I run into problems when the macro runs and tries to open an already opened workbook.
View 5 Replies View Relatedthis is a relatively straightforward query, would be obliged for any tips on same. I have the following piece of -
View 2 Replies View RelatedCreate a full copy of an open workbook (eg. activeworkbook MyFile.xls) using VBA, with the new copy (eg Book1.xls) open as well ,without having to save a copy first then open it ?
View 9 Replies View RelatedI did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
I have an Excel application in which I use the Workbook Open event to show a userform.
This works fine when Excel is not already open, but if another Excel workbook is already open, the Workbook Open event does not work.
Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.
Sub Select_Copy_Paste()
'
'
Windows("ElektroFunctiesDatabase.xlsm").Activate
Sheets("PowerSupply's").Select
Range("A2:I6").Select
Selection.Copy
[Code] .........
' Here i need to do something to paste data into r.address?
I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
I run a model in Excel that automatically saves my file every xx iterations. After saving the file I want to make a backup of the file. Tried
[Code] .....
but get a permission denied error message.
I don't want to use .SaveAs as it is a huge file that takes a while to save and SaveAs has a tendency to break links that should not be broken..
Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...
'Dim officen As Integer
'Dim thiswb As Workbook
officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
is it possible to write vb code to open the workbook, scroll through everysheet? extract the values from each worksheet?
let's just say, I only know the workbook name, but not the sheets of the name inside. So the vb code could scroll through every worksheet without knowing the worksheet name, only the workbook name(workbook.xls)
This is pretty basic I just don't know what command to use. I have a macro that's supposed to copy data from one workbook to another, it works fine but In the macro it open the other workbook then closes it. I would like the macro to not reopen the second workbook if it is already open (since reopening causes it to lose the newly updated info). I think it can be done with a If command but I don't know which tried the following: If workbooks("M:/database") Is Open but of course 'is open' doesn't work, but can anyone tell me what to use instead.
View 2 Replies View RelatedNeed some VB Code to check if a workbook is open or not. Either way, it needs to be opened and made active.
I have tried using some of the answers already on here but not getting anywhere.
