Running and Macros & Active Worksheets

[mhp620]

Hi... I'm pretty much a VBA hacker and not formally trained. So apologies if this is a rudimentary question...

I have a macro that runs fine in the workbook in which it is saved. But when I open a new workbook and try to run this macro, an "update value" dialogue keeps opening up and I have to crash Excel to break out of it.

Is there a way to run this macro without having to first move it the new workbook?

Thanks in advance, mike

 

[AMAS]

Hi Mike,

Have you tried saving it as an addin-in? That way it should be accessible to all workbooks?

AMAS

 

[mhp620]

Tried that, as well as putting it in the Personal file... I get the same error as running it from another workbook.... a Windows "Update Value" dialog wanting a choice of a file.

If I move the macro into the workbook, then it runs fine. But this is not possible operationally.

The first few line of the macro are:

Sub MY_Summary1()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Dim iCount As Integer<o></o>
<o> </o>
For iCount = 2 To ActiveWorkbook.Sheets.Count<o></o>
Range("A15").Select<o></o>
ActiveCell.FormulaR1C1 = "Asset Class"<o></o>
Range("B15").Select<o></o>
ActiveCell.FormulaR1C1 = "Fund Name"<o></o>
Sheet1.Cells((14 + iCount), 2).Value = "='" & ActiveWorkbook.Sheets(iCount).Name & "'!E1"<o></o>
.<o></o>
.<o></o>
.<o></o>
​

The dialog opens on the line starting "Sheet1.Cells((14 + iCount), 2).Value ..."

Any other advice or ideas?

Thanks. Mike

 

[AMAS]

Hi Mike,

I think I have spotted the problem. Instead of Sheet1.Cells use the sheets object with a name qualifier. Therefore Sheets(1).Cells or Sheets("Sheet1").Cells.

I have modified this bit of code and seems to work from my end:

Sub MY_Summary2()
Dim iCount As Integer
    For iCount = 2 To ActiveWorkbook.Sheets.Count
        Range("A15:B15") = [{"Asset Class", "Fund Name"}]
        Sheets("Sheet1").Cells((14 + iCount), 2).Value = "=" & ActiveWorkbook.Sheets(iCount).Name & "!E1"
    Next iCount
End Sub

AMAS

 

[mhp620]

AMAS... it worked perfectly!! Thanks. Mike