However, when I open the source workbook, the cell updates correctly. How can I get the cell's formula to update when the source data workbook is closed?
I built a template which pulls data from the source file using formulas. the problem is formulas doesn't give any error it updates the right value when the source workbook is open.
I Thought problem could be with the formula and tried just Source A1=destination A1. it also updates only if the source file is open. when the source file is closed it shows the last captured value.
The source file is on the (Work) Network Drive and is accessible to few people. my template is password protected (tabs) so formulas cannot be deleted.
I use Excel 2007 and I have an .xls that I've had set to auto-open at startup and has worked fine for months now. Alll of a sudden a couple days ago it stopped auto-opening, and I have to now manually open the .xls. I looved in the Advanced>At Startup setting and it's set to the correct folder. What other setting in Excel 2007 do I need to check that would be causing this startup problem?
There are two macros that i'd liek to add to my personal macro workbook upon startup of excel.
i tried to do them without coding but it appears that some actual coding is necessary.
1) i'd like to be able to select any # of cells anywhere on the page and hit ctrl+shift + n and have all of those selected cells read #N/A (the forumula is =na() )
2) i'd like to be able to select any number of columns (i do finance/econ research so all data is in chronological order from 1960-present along the columns. i.e. each variable is a column vector.) and be able to hit ctrl+shift+b and have VBA make a chart in a new sheet with my default settings
i've tried to do these but recording the macro hard codes the select cells for 1) and always runs into weird problems for 2).
i'm trying to put together a spreadsheet using excel 97 that uses forms for data input & display - the spreadsheet itself is used solely for data storage & no editing is done directly to ensure people aren't trying to mess about with the spreadsheet i thought it would be an idea to minimise excel when the spreadsheet is launched & have the forms pop up on screen instead. the problem i've got is getting the first form on screen whilst excel is minimised
Private Sub Workbook_Open() frmSplash.Show Application.WindowState = xlMinimized End Sub
the spreadsheet launches, the splash screen launches, but excel doesn't minimise
if i use
Private Sub Workbook_Open() Application.WindowState = xlMinimized frmSplash.Show End Sub
the spreadsheet minimises, but the form doesn't display - the excel taskbar button flashes to indicate something is going on clicking the taskbar button brings up the form & keeps excel minimised....but the staff who will be using this won't be happy with this at all!!!
I have a macro that is designed to paste a row of formulas into each Subtotal line on a 30,000+ line spreadsheet. I have specified the formulas that I want to paste in the named range "formulas". The macro below worked fine for about 3000 lines, then threw the error Run-Time Error 1004: Paste Method of Worksheet Class Failed on teh line ActiveSheet.Paste. I thought it had just bogged down my computer, so I added teh ScreenUpdating and Calculation lines, but they made no difference. I also tried reducing the range to just a couple thousand lines, which also made no difference. I also have found some suggestions on this forum and others for this error.
In addition to it working fine for 3000 lines, then failing, there are a couple other weird things. One is that on the line in the spreadsheet that threw the error, the formulas were pasted in up to column AX, leaving columns AY through CR blank. The second odd thing is that this exact same macro still works on my sample data spreadsheet (which only has around 50 records).
Sub FormatTotalRows() Dim rCell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Workbooks("latest.xls").Activate Range("formulas").Select Selection.Copy For Each rCell In Range("A3040:A5000") If Len(rCell) > 0 Then rCell.Activate ActiveCell.Offset(0, 2).Select ActiveSheet.Paste End If Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
When i add a new sheet in excel 2010 at that time i getting error that "Unable to run this command on multiple selections in vba". This error get on both through code and manually.
Having problems with the code below on a 2010 Excel spreadsheet. The function flags me at first line and highlights Mid. I get a "compile error, cannot find project or library". I'm trying to set GetSheetName = m_sSheet but the GetSheetName function is not declared as returning any type.
Code: Function GetSheetName(ByVal m_sFormula As String) As String Dim m_sSheet As String
m_sSheet = Mid(m_sFormula, 2, InStr(m_sFormula, "!") - 2) If InStr(m_sSheet, "'") Then m_sSheet = Mid(m_sSheet, 2, Len(m_sSheet) - 2) End If GetSheetName = m_sSheet End Function
I am using Excel 2010, I receivean error message "end if without block if" I've been working on this forseveral days and I have been un-successful. The code is to look at what is in combox1 and based off what is selected by the user, it will locate that tab and put in the information from the userform.
Private SubCommandButton1_Click() "S:location of file.xlsx" 'UPDATES WORKBOOK
Is it possible to have a workbook (1) with a list on it and another workbook (2) which has a dropdown list on it that shows the list on the 1st workbook. But when a new entry is made on workbook (2) it is added to the list on workbook(1)
I'm using excel 2010 and pdf creator to make a pdf (and open it automatically). It works with the following code:
[Code] .....
path and filename are previously defined objects.
I'm using pdfcreator because the code also has to be used with excel2003 on another pc. The problem is that I get an error (probably for trying to open the newly creating pdf-file).
Error 2417024894 (80070002) during execution. Method run of Object IWshSell 3 failed.
Using Excel 2010. I have a ComboBox (Not ActiveX) on a worksheet
I'm trying to add an event so that when user click on a choice a macro is fired using the selected item as citeria for a filter When I click on the drop down and make a selection I receive this error msg:
Cannot find the macro "cboUserSelection_Change" The macro may not be available in this workbook or all macros may be disabled
I also tried _AfterUpdate - similar error message.
I added the Combo Box code to the Worksheet code, not a standard module
I was able to run a quick test macro so I do not believe "all macros are disabled"
Run-time error '-2147467259 (80004005)': An unexpected token ""20*"" was found following "1' AND PERIOD = LIKE*. Expected tokens may include: "". SQLSTATE=42601 code died at RS.Open SQL, CN ///////////////////////////////////////////////////////// Sub GET_OSI Dim sn as String, osi as string, saposi As String
I'm currently running MS Excel 2003 on Windows XP SP 3. When I launch Excel from the start menu, it starts right up. When double-clicking an Excel file, it takes approximately 45 seconds to a minute to launch and open the file. This happens no matter what the file size.
I'm the final stages of testing a userform that, in response to a button click, copies certain cells from a big messy worksheet and pastes the relevant ones (based on user input) in a clean sheet. Suddenly, I started getting a 'divide by zero' error for the following line:
VB : UpCount = PickNum - 6 + ((PickNum / 12))
UpCount and PickNum are both declared as Double, though this shouldn't matter. UpCount is being assigned a value here for the first time, and PickNum varies from 1 to about 250 depending on input.
Obviously I'm only dividing by a constant here, which is VISIBLY not zero. This error only occurs for certain ranges of PickNum...something like 50-70. Interestingly, in trying to debug it, I added:
VB: Msgbox(PickNum) Msgbox(54/12)
...since PickNum was 54 as I was getting this error. Just dividing 54 by 12 ALSO got a div by zero error.
Perhaps I should mention I'm using VBA in Excel 2010 for Mac.
I extracted data from .pdf to Excel using Able2Extract. Now I need to scrub the output a bit.
I see commonalities in the data for the start and stop of each set of data that I can key in on.
Once I find the start and stop points for each set of data I would like to fill all rows in-between the points and then discard anything that remains outside of these boundaries.
I have something wrong in my logic, way too many rows are deleted.
Code: Option Explicit
Sub GetLineSets() 'Purpose: Identify relevant line sets, delete all other rows Dim wbBook As Workbook Dim wsData As Worksheet Dim strFormula As String Dim lngRows As Long Dim C As Range Dim blnFlag As Boolean
I am writing macros for a pop up calender in excel 2010. I followed instructions in the link below but at the testing step # 7 it returned; 'run time eror 424 object required'. It's my first time writing macros.
I've got a file that works fine on my computer. When I email it to anyone with 2010 and they open it from their email account (Outlook 2010), the file automatically opens up in 'Protected Mode'. If the user selects "Enable Editing" the user receives 'Run Time error 91: Object variable or with block variable not set'.
If the user closes out the vba error and saves the file to their computer and reopens the file, it works fine.
BTW, it is not a complex macro, it is error out at
Creating a spreadsheet in Excel 2010 and am creating the dropdowns from a separate sheet in the workbook. There are no spaces and I don't know why I keep getting the "The Source currently evaluates to an error."
I am entering the Data Validation to reference the first cell I need "=INDIRECT(H3)"
I have put the spreadsheet on dropbox. When you select the "Sequencing Platform" drop down, it need to give just the dropdown for the platform selected.
I have created a macro that creates a powerpoint from excel in office 2010. I have followed all the required steps, like adding object library and all. but still many times the PowerPoint crashes at slide7, 8 or 9 and have to restart. I generally get the above given error.
Code: Sub CreatePowerPoint() Application.ScreenUpdating = False 'Macro Created by Pallavi NC (pallavi.nc@hp.com) 'Add a reference to the Microsoft PowerPoint Library by: '1. Go to Tools in the VBA menu '2. Click on Reference '3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay 'First we declare the variables we will be using
I'm using Excel 2010 and I wrote the code below to autofill a range that feeds a chart on the worksheet "Dashboard." Essentially, the user selects criteria on the Dashboard and clicks the "Submit" button, which causes an advanced filter to copy the data that matches the criteria into the range Sheets("HiddenSheet").Range("A2:H"). I need the code below to autofill the formulas in I3:Q3 in I:Q until the last row in A:H, but I keep getting a "Type Mismatch" error on the bolded line below.
One note - Columns K:Q contain formulas that feed off of Column C and into Column J - that's why the autofill range is different than the chart SetSourceData range.
Sub TimelineControl() Dim Timeline As Chart Dim ws As Worksheet Dim rngforTimeline As Range Dim LastAxis As Integer Dim LastA As Long
Every time I start up excel whether it's opening a new spreadsheet or an existing one it tries to open one that I had deleted a while ago. I have nothing in my xlstartup files and I have thoroughly searched all files around it, before it, after it, and all in between. I went to Excel Options>Advanced>General and there is nothing in the field where it says "At startup open all files in:"
It seems to be trying to open the file from my desktop.
There are certain .xlsm files in our system (some have macros and others don't) which generate "Error: Source not Found" in the Edit Links dialog box when the Check Status button is pushed. However, the source location is correct, as you can click on Open Source and it will open the correct file.
For simplicity of explaining my issue, consider the following scenario. In my workbook "Corn Production Summary.xls_" I link to 2 source files: "Iowa.xlsm" and "Nebraska.xlsx" (Note: The extension of the summary file doesn't matter. This error happens regardless of what the summary is.)
When I open "Corn Production Summary" I am prompted to update values. I select Update and the file refreshes and recalculates appearing to have updated all values (i.e. no error messages). However, the values from Iowa.xlsm did NOT update and there was no error message indicating that the values didn't update.
To confirm this, I go to Data>Edit Links>Check Status in "Corn Production Summary" and I see that Iowa.xlsm has the "Error: Source not Found" error. I click on Open Source and once the file is open, my data in the summary file will update.
One work around was to do the following process (with the summary file closed):
1) Iowa.xlsm and Save As Iowa.xlsx (removing macros if needed)
2) Save As Iowa.xlsx as Iowa.xlsm (overwriting the file that is currently there and readding the macros)
3) Open the summary file and both Iowa and Nebraska work just fine now.
There are a lot of files like this, and because Excel doesn't prompt us that it isn't really getting the values from these certain .xlsm files we would have to search in "Edit Links" for each file to make sure that they really are updating.
I have to generate a report using a pivot table, that tells me what work was done on time and what was late. Entering a formula outside the pivot table sees the scheduled start date drop downs as "blank" and says they are late when they are not. I cannot seem to use the value fields when I try to create a calculation field within the table. I use 2010 at work, 2007 at home.
I have tracked down an overflow Error 6 to the following line:
dMax = Int(s * DPoints / SPoints)
Where all variables are defined as Integers. The error goes away when I define s as Long, but I don't understand why this should be required since none of the individual variables ever exceed the scope of an Integer (-32768 to 32767).
Strangely, I get the same overflow problem when I type either of the following calculations in the immediate window:
Note that no variables are defined, so why the overflow problem? Surely Excel/VBA should be able to work with this very basic calculation without any problem, by assigning the appropriate dimension, no?
Is this a known problem with Excel/VBA?
I am using Windows 7, with Excel 2010 (updated from 2009).
