Free Microsoft Excel 2013 Quick Reference

Write to another workbook Results

Long time no see.

At the office, we have two versions of Office, 2003 and 2007. I write vba with 2007 and other users have 2003. This gives naturally some compatibility problems, but my main concern now is the following: When I instruct my program to save a newly created workbook, it is saved as .xlsx by default. I need to add FileFormat:=56 in order to save it in 2003 format. Unfortunately the users who execute this program in their Excel 2003 encounter an error because of this FileFormat parameter.

How can I make the code to check the users Excel version, so I could write two different saving methods for two different versions into an If - Then - Else routine? Or is there another way around this problem?



I know it may sound trivial to most of you, but could someone explain to me what steps I need to take to run a macro? For example, how would I go about writing a macro to create another worksheet in a workbook with the next day's date as the tab name (automatically) with the next date being the same worksheet, but with certain cells left blank so that new data can be entered for that date.

Hey all,

I wrote a macro that helps take a daily task from about 2 hours to about 5 Mins. I am trying to bring that down to 1 click.

My original code opens 11 files, and copies the data into 1 workbook. From there, i have 4 sheets that summaries the data, and i copy that into another workbook.

That is what i would like to try to do now. the issue I have is, because the data im copying in goes to specific colums, i am unsure how to write that. so im hopeing somene can help me here.

This macro assumes that the file " Blank All Agencies Worksheet.xls" is open.

here is a brief idea of what im trying to achive
' final steps to updating Cash Deposits.

    ChDir "O:Accounts ReceivableAR ReportsCash Deposits"
    Workbooks.Open Filename:= _
        "O:Accounts ReceivableAR ReportsCash Deposits2007 Cash Flow - Current.xls" _
        , UpdateLinks:=3
 Window("BLANK all agencies worksheet.xls").Activate
Sheets("CDN SUM").Select

Window("2007 CASH FLOW - CURRENT.XLS").Activate

'This is where i need help.  In ROW 4, there are Dates. I need to paste (values only) the copied data
'in ROW 5, Below the cell with the date that matches ("H1") from BLANK all agencies worksheet.xls
'on CDN SUM sheet.

End Sub
Can anyone help me with that last part? i will then alter it to make the next 4 sheets do the same thing

Thanks much for any help.

As a novice vba'er, I lookup and use existing code segments...I have the following that works well to yank out information from ms-project, and dump it into excel for analysis...but, an object I also need seems to be in another 'class'..??... how to I add the Task.Predecessor object into my vba code? (see line " xlCol = Task.Predecessor"...??

thanks for the help!

'This module contains macros which will export
'tasks to excel and keep the task hierarchy.
'modify as necessary to include other task information

'Copyright Jack Dahlgren, Feb 2002

Option Explicit
Dim xlRow As Excel.Range
Dim xlCol As Excel.Range
Sub TaskHierarchy()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim Proj As Project
Dim t As Task
Dim Asgn As Assignment
Dim ColumnCount As Integer
Dim Columns As Integer
Dim Tcount As Integer

Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate "Microsoft Excel"

Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "Project Dump"

'count columns needed
ColumnCount = 0
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If t.OutlineLevel > ColumnCount Then
ColumnCount = t.OutlineLevel
End If
End If
Next t

'Set Range to write to first cell
Set xlRow = xlApp.ActiveCell
xlRow = "Filename: " & ActiveProject.Name
dwn 1
xlRow = "OutlineLevel"
dwn 1

'label Columns
For Columns = 1 To (ColumnCount + 1)
Set xlCol = xlRow.Offset(0, Columns - 1)
xlCol = Columns - 1
Next Columns
rgt 2
xlCol = "work" & "Hrs"
rgt 1
xlCol = "actual work" & "Hrs"
rgt 1
xlCol = "Resource Name"
rgt 1
xlCol = "Task ID"
rgt 1
xlCol = "Predecessor"

Tcount = 0

'Write Columns
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
dwn 1
Set xlCol = xlRow.Offset(0, t.OutlineLevel)
xlCol = t.Name
If t.Summary Then
xlCol.Font.Bold = True
End If
For Each Asgn In t.Assignments
dwn 1
Set xlCol = xlRow.Offset(0, Columns)
xlCol = Round(((Asgn.Work / 480) * 8), 1)
rgt 1
' xlCol = (Asgn.Work / 480) & " Days"
xlCol = Round(((Asgn.ActualWork / 480) * 8), 1)
rgt 1
' xlCol = (Asgn.ActualWork / 480) & " Days"
xlCol = Asgn.ResourceName
rgt 1
xlCol = Asgn.TaskID
rgt 1
xlCol = Task.Predecessor
Next Asgn
Tcount = Tcount + 1
End If
Next t
AppActivate "Microsoft Project"

MsgBox ("Macro Complete with " & Tcount & " Tasks Written")
End Sub
Sub dwn(i As Integer)
Set xlRow = xlRow.Offset(i, 0)
End Sub

Sub rgt(i As Integer)
Set xlCol = xlCol.Offset(0, i)
End Sub

My job requires me to do a lot of copying of data that is a standard and then pasting it into another sheet of my workbook. We are lookint to streamline this using a macro that will automatically go to the desired sheet, select data, copy it, then return to the main sheet. I would like to use a button on the sheet to perform this function instead of Crtl-?. I do not know what code to write.

Guru's, questions for you.

- If I have an excel workbook open and then, select the side-by-side option to open another workbook, and I want to write a macro that will switch between the two - how can I do this? The problem is, I don't want it to switch by file names (one workbook constantly changes names), I just want it to switch between active workbook and inactive workbook.

- Once I have the code to switch between active workbook and inactive workbook, how can I have my macro look at a cell and see if data is present and if so, drop down to the cell below.

Here is a brief macro I'm trying to write for the above. I'll take any suggestions anyone has!! Thanks in advance!!

Windows("Job Number Request form.xls").Activate
Windows("Backup Copy of 2007 Capex Job Log.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Hello all;

I was looking for macro code to copy worksheets within a workbook. I use XL97 and Windows 2000. I have tried this with XL97/Win 2000 and XL2000/Win XP. No success.

I found the following code on this web page, copied it, and it does not run. The macro stops at the following line:

Sheets(LastSheet +1).Name = ThisTerr

The entire tip/macro is pasted below. Any ideas why this will not work?

Past Tip of the Week

"Stuck in NJ" asked this week's question. I am creating a workbook that contains charts of sales figures. The first worksheet corresponds to the first sales territory in the company (eg. FL01.) The worksheet's tab name (as well as cell A1) is the sales territory number FL01. The data that drives the charts is to the right(outside of the printable range that I set) and is populated from another excel workbook using VLOOKUP with the sales territory number FL01 in cell A1 as the lookup key. I need to add to the workbook 76 identical worksheets (one for each sales territory) such that each sheet's tab name and cell A1 equals successive sales territory numbers (eg. FL01, FL02, LK01, LK02.) How do I do that in an automated way?

This is easily accomplished with a VBA macro, but let me cover the tip for doing this manually. Once you have a sheet set up with formatting and print ranges, you can easily duplicate the sheet by right-clicking on the tab for that sheet. From the menu that pops up, choose Move or Copy. From the Move or Copy dialog box, select "(move to end)" and check the "Create a Copy" box, then click OK. A copy of your sheet with formatting will be added as a new worksheet in your workbook. You will usually want to rename the sheet. Right-click the new tab, choose rename, and type a meaningful name.

For the Excel macro to work, it needs a list of territories. Before you write the macro, insert a new sheet in this book named Data. In Cell A1, enter the 2nd sales territory (FL02 in your example). Continue entering all of the sales territories down column A. Do not leave any blank rows.

Insert a new macro and copy this code:

Public Sub CopyIt()
' Determine how many territories are on Data sheet
FinalRow = Range("A65000").End(xlUp).Row
' Loop through each territory on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count
ThisTerr = Range("A" & x).Value
' Make a copy of Fl01 and move to end
Sheets("FL01").Copy After:=Sheets(LastSheet)
' rename the sheet and set A1 = to the territory name
Sheets(LastSheet + 1).Name = ThisTerr
Range("A1").Value = ThisTerr
Next x
End Sub

The macro makes use of the Sheets().copy command which does the same thing as clicking Move or Copy manually. Before doing each copy, I find out the number of sheets by using the Sheets.Count property. I know if there are 56 sheets and I insert a new sheet, it will be referenced as Sheets(57). I find it somewhat unpredictable to guess how Excel will name the new sheet, so I use numeric index numbers to identify the sheet. Once I rename the new sheet with "Sheets(LastSheet + 1).Name = ThisTerr", I can then switch back to my preferred method of referring to the sheet by using its name.

Note that the list of territories should not contain FL01 in the list. Trying to make a rename a copy of FL01 with the same name will cause an error in the macro.

I have a workbook containing 33 worksheets and a very large amount of macro code. This workbook is about 1.4 mb.

By making various entries and choices the user has a minimum of 3 and a maximum of 4 pages that they need to save. In order to reduce file size I am copying these pages to a new workbook(template) and then saving this new workbook. The original workbook is not saved. The problem is that when the user opens the saved book if they click on any of the buttons they get a message saying (workbook1) macro not found. I need to write a macro that would assign a new macro to the buttons. (a pop up message telling the user they were disabled).

My problem here is that sometimes there will be 4 sheets and sometimes only 3. The sheet names will vary as will the number of buttons on each sheet. Another problem I am finding is that since I sometimes did a cut and paste of buttons often more than one is called button one (these were created from forms) so I could have say 3 button 1’s, 1 button 7 etc (incidentally can you rename these buttons).

Is there a simple way to produce a macro that will select each sheet in turn, determine the number of buttons on that sheet then assign a new macro to them?


Any help would be much appreciated on this vexing problem.

I'm using .find on a range object and searching for a date every time I run my macro (on business days only). The purpose is to find a date in a column of chronologically sorted dates and copy anything below to another sheet. I use the most recent date on that other sheet to determine from where to copy and paste. There is data in the next column, and that is copied also, but that is irrelevant for now.

I wrote this macro on Nov 1 and it worked every day up until the dates went double digit, i.e., after the 9th. I've double checked that the true excel date values are the same and that the formatting is the same between the sets of dates, etc. I can test the macro as if it were still pre-Nov 9th and all works well.

Even more interesting is that the macro works, post Nov 9th, if I use the debugger, but it makes no sense as to why. It works if I set a breakpoint and use that opportunity to so much as select a single cell on the worksheet on which I am searching, so long as that cell is within the range of dates I am searching or the next col containing the data to be copied. This is impractical, of course, as I loop through several files to be imported in said manner and it would partially defeat the whole point of writing a macro to do this for me. And this does not happen if I select any other cells on the sheet in question. Why this should have the observed effect is beyond me.

I have also checked to ensure that there are no duplicates of the date for which I am searching, and that the date actually exists. Using the debugger, I can also stop the code and manually alter the date for which I am searching, and again, it can find any date I throw at it in any month or year so long as the day is the 9th or earlier of any month. But why?

Again, thank you for any help.

Some of the code (relevant portion) is below:

For a = 1 To 18
NameOfRange = Cells(7, (a + 1)).Value 'obtain names from excel sheet
Set DestinationDateRangeObject = Range("Date") 'prior named range
Set DestinationDataRangeObject = Range(NameOfRange)
'each name has a corresponding named range on a sheet to which
'we paste data

Workbooks.Open Filename:=(LongPath), Format:=2, Delimiter:=","
'line above opens proper file that has data we want to copy
'longpath is defined elsewhere and is visible etc
'lines below reflect naming convention of imported files and works
'without a problem

LastDataRow = Workbooks("H15_" & NameOfRange & ".txt").Worksheets("H15_" & NameOfRange).Range("A65536").End(xlUp).Row

Set SourceDateRangeObject = Workbooks("H15_" & NameOfRange & ".txt").Worksheets("H15_" & NameOfRange).Range("A8:A" & LastDataRow)

'note that headers occupy first 7 lines that we ignore

Set SourceDataRangeObject = Workbooks("H15_" & NameOfRange & ".txt").Worksheets("H15_" & NameOfRange).Range("B8:B" & LastDataRow)

'first determine most recent date in destination
'worksheet to compare against most recent date we already have

'date of most recent observation from imported file obtained on next line

LastDataDate = Workbooks("H15_" & NameOfRange & ".txt").Worksheets("H15_" & NameOfRange).Range("A" & LastDataRow).Value

NoOfDaysSinceLastUpdate = DateDiff("d", LastDate, LastDataDate)

'returns a positive # so long as LastDataDate is more recent than LastDate
'above number would equal how many rows to copy but for the fact that
'the dates are business days not calendar days so above lines not needed
'but do work

'determine where to start copying by matching LastDate+1day to list of
'dates in LastDataDate

Set c = SourceDateRangeObject.Find(LastDate, LookIn:=xlValues)
AddressOfLastDate = c.Address '

I was writing a macro in my personal macro workbook while another excel file was open. I saved my work in the personal workbook and then quit excel. A msg popped up to ask me if I wanted to save the other workbook and I said no.

The problem now is that when I open excel, I can't see the personal workbook. When I try to record macros in it, I get a message that says "Personal macro workbook in the startup folder must stay open for recording."

How do I get my personal macro workbook back and what did I do to make it go away?

I have been working on this project for sometime now. Its my first thing Ive done in VB

I currently have all this working code

Sub eMailActiveWorksheet()
    Dim OL              As Object
    Dim EmailItem       As Object
    Dim Wb              As Workbook
    Dim FileName        As String
    Dim y               As Long
    Dim TempChar        As String
    Dim SaveName        As String

RowCount = InputBox("How many rows in spreadsheet?")

MsgBox ("Select email template")
Dim MyFile
    MyFile = Application.GetOpenFilename("All Files,*.*")

Range("a31") = MyFile

For i = 2 To RowCount
rangevarname = "g" & i
rangevarItem = "d" & i
rangevartrack = "f" & i
rangevaremail = "c" & i
rangevarURL = "h" & i
rangevarItemno = "e" & i
rangevarbuyer = "o" & i
rangevar1 = "i" & i
rangevar2 = "j" & i
rangevar3 = "k" & i
rangevar4 = "l" & i
rangevar5 = "m" & i
rangevar6 = "n" & i
rangevardate = "a" & i

strName = Range(rangevarname)
strItem = Range(rangevarItem)
strTrack = Range(rangevartrack)
strEmail = Range(rangevaremail)
strURL = Range(rangevarURL)
strBuyer = Range(rangevarbuyer)
str1 = Range(rangevar1)
str2 = Range(rangevar2)
str3 = Range(rangevar3)
str4 = Range(rangevar4)
str5 = Range(rangevar5)
str6 = Range(rangevar6)
strDate = Range(rangevardate)

    Application.ScreenUpdating = False
    Set OL = CreateObject("Outlook.Application")
    Set EmailItem = OL.CreateItem(olMailItem)
    FileName = ActiveSheet.Name & " - " & ActiveWorkbook.Name
    For y = 1 To Len(FileName)
        TempChar = Mid(FileName, y, 1)
        Select Case TempChar
        Case Is = "/", "", "*", "?", """", "", "|"
        Case Else
            SaveName = SaveName & TempChar
        End Select
    Next y
    Set Wb = ActiveWorkbook
    Wb.SaveAs SaveName
    Wb.ChangeFileAccess xlReadOnly
    With EmailItem
        .Subject = "Important tracking information for Item #" & Range(rangevarItemno) & " - " & Range(rangevarItem)
               .HTMLBody = LoadTemplate(strName, strItem, strTrack, strEmail, strURL, strBuyer, str1, str2, str3, str4, str5,
str6, strDate)
        .To = Range(rangevaremail)
        .Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
        '.Attachments.Add Wb.FullName
    End With
    Kill Wb.FullName
    Wb.Close False
    Application.ScreenUpdating = True
    Set Wb = Nothing
    Set OL = Nothing
    Set EmailItem = Nothing

End Sub

Function LoadTemplate(strName, strItem, strTrack, strEmail, strURL, strBuyer, str1, str2, str3, str4, str5, str6, strDate)
    Dim fso As New FileSystemObject
    OpenMyFile = Range("a31")
    Dim f As file
    Dim fsoStream As TextStream
    Dim strLine As String
    Set f = fso.GetFile(OpenMyFile)
    Set fsoStream = f.OpenAsTextStream(ForReading)
    Do While Not fsoStream.AtEndOfStream
        strLine = strLine & fsoStream.ReadLine
    Set fsoStream = Nothing
    Set f = Nothing
    Set fso = Nothing
    strLine = Replace(strLine, "#USERNAME#", strName)
    strLine = Replace(strLine, "#ITEM#", strItem)
    strLine = Replace(strLine, "#TRACK#", strTrack)
    strLine = Replace(strLine, "#EMAIL#", strEmail)
    strLine = Replace(strLine, "#BUYER#", strBuyer)
    strLine = Replace(strLine, "#URL#", strURL)
    strLine = Replace(strLine, "#1#", str1)
    strLine = Replace(strLine, "#2#", str2)
    strLine = Replace(strLine, "#3#", str3)
    strLine = Replace(strLine, "#4#", str4)
    strLine = Replace(strLine, "#5#", str5)
    strLine = Replace(strLine, "#6#", str6)
    strLine = Replace(strLine, "#DATE#", strDate)

LoadTemplate = strLine

End Function

This basically emails my buyers with all their info after hey have bought an item off me from ebay

However - there is 1 scrappy bit. It writes the path of the email template to a cell because I cannot refer to soething in the sub from down in the function. Then the function reads that cell

Ive heard of making it public. Im not 100% sure how this works.
Can i just put Code:
 at the top of everything?

I know Id have to change some bits to suit that - Id comment the read/write to cell bits

Another solution would be to read/write to a text file. I have managed the write bit sucessfully but cant find the right code to read

Thanks for any help!!

You should always post your code for comments.
If you close the workbook where the application.quit is how can it work?
could be the problem.

Don Guillett
SalesAid Software
Granite Shoals, TX
wrote in message
> I have a workbook with public macro to open another
> workbook; paste a value; close the second workbook; write
> to a database; save the first workbook; quit excel. I
> have tried the appication.quit without success. Is there
> another command? Any suggestion is appreciated.

I am trying to automate the copy process of rows from one worksheet to
another based on specific data. I am running a DB query and returning the
data to my workbook. I then want to copy certain rows to another sheet based
on specific values in the first column. The data being returned is Date and
Time formatted as follows: mm/dd/yyyy 13:00 and is returned in ten minute
I want to automate copying rows from this worksheet to another worksheet
based on specific times in the first column. These values are not always in
the same rows.
Can I write a macro to copy the specific rows based on the time entered in
column one? If yes, what is the proper syntax?


I tried the macro solution - it works! I can read the entire 5005 row x 42
column file in about 3 seconds. Now I have confusion about the process.
Where is the macro stored? I created a folder with 2 files - the .CSV file
to be read and the empty .XLS file with the name I want it to have. I
invoke it by pressing ctl-m. I'll change that to Auto_Open so it will run
when I open the .XLS file. When I move the folder with the 2 files to
another computer, the macro doesn't work. So the macro didn't come with the
..XLS file.

I don't know what you mean about the "You could plop the date into a cell in
a hidden sheet so that it only runs once. And add a save at the end".
Also, I want to delete a macro so I can re-record it, but it talks about
un-hiding something. What?

Thanks for your help.
================================================== =======

"Patrick Molloy" > wrote in message

the macro recorder will show you the very basic VBA code. you'll want to
tidy up
but its a great way to start

"Paul H" > wrote in message
> I tried it using Excel 2003 and it works there also. I'll try the entire
> process tomorrow (Monday). Wish me luck, and thanks.
> Paul
> ================================================== =======
> "JLatham" > wrote in message
> ...
> In Excel 2007 you will need to be able to see the [Developer] tab. If it
> is
> not visible now, click the Office Button, then the [Excel Options] button
> near the lower right of the window that opens. In the "Popular" group,
> enable the "Show Developer tab in the ribbon" option.
> To record a macro, choose the Developer tab and click the "Record Macro" -
> give it a name and procede with the steps you wish to record. The "Record
> Macro" option will have changed to "Stop Recording". Click it when you
> have
> finished recording the steps you need to repeat later.
> "Paul H" wrote:
>> ================================================== =======
>> How do I record a macro? Do you mean I can create a macro that will do
>> my
>> steps 1 thru 8, below?
>> ================================================== =======
>> "Dave Peterson" > wrote in message
>> ...
>> It's too difficult to push a button?
>> If that's true, then name your macro Auto_Open. It'll run the first time
>> someone opens the workbook. You could plop the date into a cell in a
>> hidden
>> sheet so that it only runs once. And add a save at the end.
>> If that doesn't work, good luck with the automation.
>> Paul H wrote:
>> >
>> > ================================================== =======
>> > I need the COBOL program to properly format the data. Multiple
>> > end-users
>> > will use this, sometimes daily, with data that changes daily, so I have
>> > been
>> > requested to make it completely automatic. Another subsequent program
>> > shows
>> > them the choices of reports and automatically starts the one they
>> > select.
>> >
>> > > ================================================== =======
>> >
>> > "Dave Peterson" > wrote in message
>> > ...
>> > Maybe you could drop the requirement that the COBOL program do it.
>> >
>> > Set up a workbook with two sheets (instructions for the user and the
>> > actual
>> > data).
>> >
>> > Record a macro that does all the work in the second sheet.
>> >
>> > The plop a button from the Forms toolbar onto the instruction sheet
>> > that
>> > calls
>> > that macro.
>> >
>> > You only have to rename the worksheet if you're doing File|Open (or the
>> > equivalent in code). If you use the import external data stuff, you
>> > can
>> > leave
>> > it named .csv.
>> > >
>> > ================================================== =======
>> >
>> > Paul H wrote:
>> > >
>> > > Thanks Dave,
>> > >
>> > > I used your "import text" method and it does succeed in importing my
>> > > .CSV
>> > > file into my empty .XLS file, in about 2 seconds. That is, after I
>> > > manually:
>> > >
>> > > 1. Open empty XLS file.
>> > > 2. Data, import external data, import data.
>> > > 3. "Select Data Source" screen comes up.
>> > > 4. Type in my xxxx.TXT file name.
>> > > 5. Import wizard step 1 - select delimited, then next.
>> > > 6. Import wizard step 2 - select only Comma, then finish.
>> > > 7. Import data to existing worksheet.
>> > > 8. It imports the entire file in about 2 seconds, formatted
>> > > correctly,
>> > > with
>> > > columns as described in my empty .XLS file.
>> > >
>> > > Now I need to figure out how to accomplish these steps automatically
>> > > by
>> > > my
>> > > COBOL program.
>> > >
>> > > Thanks again,
>> > > Paul
>> > >
>> > > ================================================== =======
>> > > "Dave Peterson" > wrote in message
>> > > ...
>> > > If you rename the .csv file to .txt, you may be able to import the
>> > > file
>> > > quicker
>> > > by setting each field the way you want.
>> > >
>> > > Then you could format the numeric fields as percentages or whatever
>> > > you
>> > > needed.
>> > >
>> > > If you have fields that have implicit decimals, you could import them
>> > > as
>> > > Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty
>> > > cell.
>> > > Edit|copy, edit|paste special|Values and divide (all in code) to
>> > > convert
>> > > that
>> > > field.
>> > > ================================================== =======
>> > > Paul H wrote:

I have an empty, formatted spreadsheet, that I created by writing a few rows
into it from my COBOL program, field by field, then deleting all of the

The process of creating the entire XLS or XLSX runs much too slow (100
records per minute or less), and uses memory up, so can never allow me to
finish converting some of my large CSV files into formatted XLS (Excel 2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS empty
formatted file? I cannot find a way. Any help would be appreciated.

TIA, Paul
>> > >
>> > > --
>> > >
>> > > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>> --
>> Dave Peterson

I have a .NET application that, among other things, creates Excel
workbooks, and I have run into a very strange problem involving
formulas on one worksheet that reference values on another worksheet.
The text I write into, let's say, cell A25 on Sheet1 (using .NET)
looks something like this:


On the completed workbook this turns into:


This formula works fine when I type it in by hand, and I've been
using the RC[] syntax in .NET without trouble for quite a while
(although up until now all my formulas have only referenced their
own worksheet). But when I create this exact same formula using
..NET I get a #NAME? error!

There is nothing wrong with the text in the cell. Forcing the
workbook to calculate using F9 doesn't help. But if I double click
on the cell as if I were going to add something to the formula,
and then move away without adding anything, suddenly the formula
works! (There is further weirdness involving what happens if I
now try to drag the apparently working formula across other cells,
or when I save the workbook, but it's kind of hard to describe, so
I won't go into it here unless someone wants to know).

I've been able to get around the problem for now by using a named
range on Sheet2 instead of saying 'Sheet2'!A:X, but I'm nervous
about this, and I need to understand what is going on. I have not
been using the Formula or FormulaR1C1 properties to create formulas
-- so far I've just been assigning text to a cell, and nothing more
has seemed necessary, even for formulas. Could that be what's
causing the problem in this case, when the formulas involve another
worksheet? And if not that, then what might it be?
John Brock

A vlookup is simple in it's capabilities, but I want to get clever.

I'm looking up a value from another workbook. Simple enough in itself, but
heres the thing.

I'd like to write a dynamic vlookup that checks the value of another cell to
find the table array for it to look up from.

Basically I have a table of data, the column headers are dates. I have a
report that is run daily that I need to look up from. Rather than implicitly
referencing each sheet I'd like to tell the vlookup to check the date in the
column header and use that for the sheet. The range inside of the sheets is
always the same.

Any help would be appreciated.


I am writing an addin, and I am using conditional compilation
#Const and #If
to produce different versions.
Is there a way of autmationg the process of setting the constants?
In other words, can I create an additional modul containing
code which changes the constants
or can i create another workbook doing this?
Additionally, depending on the compile constants,
some references to OCX objects have to be set
or not set from the Tools->References menu. Can that also be
done through additional code?

I really need to produce 3 different version of this addin,
and I would like to automate this.

I am writing an excel macro (first time doing this) which pulls in
company credit ratings from a Bloomberg terminal, compares the credit
ratings with ratings in a database, and writes any updated credit
ratings into the database. The code works fine, but now I want to have
the sequence run once a week (say, every Monday morning). This seems
easy enough to do by setting a windows event that will open the excel
workbook every Monday morning and then setting the macro sequence to
execute when the workbook opens. Then I would just add in a line
telling the workbook to save itself and close excel once the sequence

The problem is that several dialog boxes appear when the workbook is
open. The first asks the user whether to enable macros. I've gotten
around this by setting low security so that macros are always enabled.
The next dialog box to appear asks the user whether to allow this
workbook to pull in values from another workbook. Then there is
another dialog box that asks the user to approve opening an external
program that pulls in data from Bloomberg.

I need to find some way to automatically approve these dialog boxes.
I've tried playing with the SendKeys command, but with no luck.

chriskane's Profile:
View this thread:

I've never seen xl behave this way.

Is there some chance that something else is happening?

Maybe when you store your information???

If you store your info in the same workbook (different worksheet), does your
store_db procedure turn off application.enableevents?

Nils M wrote:
> In my application code the change event of the worksheets is used to
> store the values of the changed cells in a database.
> Unfortunately if the user enters a formula in one cell and goes
> directly to another cell e.g with the cell dows key, the calculation of
> the formula is not completed before the event handler is fired and the
> value of the cell is set to "0" although the formula property is
> already updated. This effect can only be reproduced on slow machines
> (like my PC).
> Any idea how to force Excel to complete the calculation of the changed
> range?
> My second idea is to read the formula property and calculate it in the
> code. But I havenot found a solution for this without writing a
> complete parser for all Excel formulas.
> Kind regards,
> Nils
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
> Range)
> Target.Calculate
> 'v = Target.Value => "0"
> Call store_db(Sh.Index, Target)
> End Sub


Dave Peterson

Hi, I have recorded a macro which opens a new workbook called 'wizdatasave',
then the macro selects and copies 2 worsheets from another open workbook and
pastes them into the newly open workbook 'wizdatasave'. What code do I enter
into the macro to automate the 'Save As' function? I need to save the
'wizdatasave' workbook as something different each time it is opened.