I've been using the following bit of code to run a macro at the specified time:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application .OnTime TimeValue("12:00:00"), "GetData"
The GetData sub executes a shell script and then pulls data from the result. The problem is that the macro runs 140+ times, and so I get 140 windows popping up and the system practically stops. I can't figure out why this is happening, as there are no loops or any sort of repetition in the code. Any help is greatly appreciated as this problem occurs with more than just the one spreadsheet.
I have 2 workbooks open and I want to set up formulas from one linking to the other. When I toggle on the task bar between the 2 workbooks they replace each other within the same window so I can only see one workbook at a time. How do I set it up so I can have 2 separate windows open at the same time, one with each workbook in it? I tried 'side by side' in one window but the viewing area is too small.
Any way to open a single instance of excel in two different windows. I use multiple monitors, and I like to have one worksheet maximized on one screen, and another worksheet maximized on the other, but when both worksheets are open in the same instance of excel this does not seem possible. Also, I don't want to use two separate instances, because then I can't paste special from one worksheet to the other.
Sub OpenCalendarForm() frmCalendar.Show vbModeless
When I run this I get this halt "System error&H80070057 (-2147024809). The parameter is incorrect. I havn't been doing VBA in a while as I am retired now but I have used this particular statement many times in the past and never had this error before. I have made sure there no typos. I open another workbook which has forms opening and copy pasted the the routine into my other workbook but I get the same error.
With excel 2013 you're now able to open multiple windows or views of the same workbook. However when a workbook is saved with multiple windows open, the next user to open the spreadsheet will also open it with multiple windows. Which can be very annoying when most people work off of one window vs. multiple. Is there a way to disable saving the multiple windows or a macro to force open excel in 1 window?
I have written this macro which I run about 1000 times in a loop. It runs solver and copies the results to a row in my spreadsheet so i end up with a sheet of solver results.
The problem is that every time it runs it eats up memory and slows down. I timed it and the first 100 runs take about 2 minutes and the last 100 takes about 15 minutes, the ram use increases by about 4gb in that time too.
As this is the first VBA code I have written and I have been learning as I go along, I assume its something I have done wrong in the code.
I'm working on a spreadsheet which needs to refer to the current date. My PC checks the system date/time daily. Can I assume that if an end-user regularly connects to the internet, their PC will have a correct system date? Does anyone know if Win2000 or Win98 automatically update the system date/time when connected to the internet?
Is there a faster method than this code? it slows way down when the range is expanded b/c it loops through every cell.
Private Sub Worksheet_SelectionChange(ByVal target As Range) If target.Cells.Count > 1 Then Exit Sub Set Rng = Range("A1:GA400") clrind = 42 If Not Intersect(target, Rng) Is Nothing Then For Each cell In Rng If cell.Interior.ColorIndex = clrind Then cell.Interior.Pattern = xlNone Next cell End If End Sub
I have a macro running in Word (and referencing Excel) that is meant to iterate 15,000 times. But, after 100 times, the process slows and stalls.
I have put in a few "DoEvents" and "ActiveDocument.UndoClear" in Word, and I set "Application.EnableEvents = False" in Excel. These settings got me up to 100 iterations (previously the program would stop after even fewer )
Does anyone have any ideas or suggestions? I'm not sure what information you'd need to be informed about the problem, and I don't want to tell you too much! Do please ask if I'm not being clear.
I have an AutoFilter list of 14,000 rows by 14 columns, and the cells have some specific formating: fill color, font, protection status, wrap, etc....
There are an additional 7 columns of formulas to the left of the filtered range.
The strange thing is---- -WITH the formating, trying to Unfilter the list takes 2 min, via a manually activated Data>Filter>ShowAll OR via a macro run of 'ActiveSheet.ShowAllData' . (In an attempt to optimize speed, the VBA macro sets calculation to manual before the 'ActiveSheet.ShowAllData' and screen updating set to false.)
-WITHOUT the cell formating (eg. by doing Edit>Clear>Formats), the ShowAll takes about 3 sec.
Does anyone have experience or an explanation for this?
Why should the Formating affect Filtering so much?
Options for improving speed of autofilter?
I don't know if, or why it would be a factor, but note that I am using Dynamic Named Range and VBA to expand/contract the formulas
and formating to size of the list/table. Although this is not done during the filtering use.
Here is the dynamic formating code
Application. ScreenUpdating = False With Application .Calculation = xlManual End With
I am wondering if some strange 'artifact' of manipulating the formating is becoming a factor?
I've created a Excel workbook with VBA code to synchronize an Access database between a laptop and a server. One of the fields in the database is a shortcut to a jpg for each record. (The Access stuff is not too important here)
Now I'd like to add the ability synchronize the photos between the laptop and the server; so people who use the server can view the photos added by the laptop and vice versa.
Code: For each [JPG] in [LaptopFolder] If not [JPG] exists in [ServerFolder] then filecopy [LaptopFolder][JPG], [ServerFolder][JPG] end if next [JPG]
I'm trying to run a macro on my machine (Windows 2000 and Office 2003) and I keep getting an error on one method. Now I tried to run the same macro on a different machine (Windows XP and Office 2003) and it didn't error out.
Public strControlTitle As String Public strControlTime As String Public strControlUnit As String Public intControlItem As Integer Public intLastNameOnly As Integer Public intDataType As Integer
Sub Main() Dim strControlItem, strPageName, strValue, sigma, z95, z99 As String Dim r, c, count, cmax, rmax, rt, ct, low, high, a, b As Integer Dim lesscol As Integer With ControlChartForm .ComboBox1.AddItem ("Average Turn Around Time All Patients")....................
I like the look of when only the used columns and rows are shown. I like to hide all unused columns and rows, and have the background and a minimalist spreadsheet.
HOWEVER, is it just me, or does Excel move a lot slower when thousands of rows and columns are hidden? Particularly, opening files seems to be slower. I'd love to delete them entirely from existence, so Excel only has a few rows and columns to work with, but that doesn't seem to be an option.
I export raw data from my accounting system each month that I then format for use in another application that uses the data to produce customer statements. I have attached a file that shows the raw data in the first sheet and the formatted data in the second sheet. I recorded a macro while I did the process but I need to change the code to deal with dynamic data as the number of rows may be different every month.
Here are the steps I go through: 1. Clear the first 3 rows and the last 5 rows 2. Copy the totals in the last row of the data and paste them in the first row 3. Subtotal the different categories in row 2 and add a validity check 4. Add a new column A with a formula to add customer numbers on each line =IF(ISERROR(FIND("00000",B5)),A4,B5 5. Copy the formulas and paste values over them 6. Filter the data to show blanks under the "Doc Date" column, delete all rows 7. Filter the data to show blanks under the "Type" column, delete all rows 8. Filter the data to show "Total:" under the "Apply No" column, delete all rows 9. Turn off filters, format all numbers in accounting format 10. Check the validation at the top to ensure no transactions were deleted
i have a spreadsheet with data that is exported to Excel via our in house investment system, the report looks something like below, though real data consists of 2000 rows of data. Where we have O/S in Bank this means these entries are all physical bank entries i.e statement credit and statement debit, and where we have O/S not in Bank these are all accounting entries, i.e. Ledger Debit and Ledger Debit.
What i am after is a macro that will insert a column next to Team and then input SDR SCR on all statement entries and LDR and LCR on all Ledger entries, the final report should look like the second spreadsheet....
I have written a few macros, which use date type variables. Because VBA uses the 1900 date system, and some workbooks use the 1904 date system, I have to first check and see if a workbook uses the 1904 date system, and if so, subtract off 1462 days where appropriate. My macros used to check the date system by using the command:
If ( ActiveWorkbook.Date1904 = True) Then nh_stringToDate = nh_stringToDate - 1462 End If
But, sometimes the macro resides in one workbook (call it macro.xls) but the currently active workbook is some other workbook (something.xls). What I really care about is the date system of macro.xls and NOT something.xls. So, instead of "ActiveWorkbook" is there some other object I can use to refer to the workbook in which the macro itself resides? I don't want to rely on using the name of the macro workbook (macro.xls) because this could change! So I need a way to simply refer to the workbook in which the macro resides.