I want to copy to the next workbook,
but some code I found doesn't even start to work.

At http://www.cpearson.com/excel/sheetref.htm
it talks about worksheets but I get stuck at:
Application.Volatile True
v = Application.Caller.Parent.Index
which gives an 'object reqd' msg.

also
i = Application.Caller.Parent.Parent.Worksheets.Count
fails the same way.

I just did the latter with a copy/paste,
so it's not a typo problem.

I'm calling the macro from the spreadsheet with alt-F8.

Hope you can help,
Peter.

his example is probably coded to be called from a formula in a cell
THEN the application caller will identify the cell it was called from.
and the application.caller.parent will id the sheet it was called from.

=sheetposition() typed in a cell will have the functon work,
but it cannot be called from a macro.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Peter Chatterton wrote :

I want to copy to the next workbook,
but some code I found doesn't even start to work.

At http://www.cpearson.com/excel/sheetref.htm
it talks about worksheets but I get stuck at:
Application.Volatile True
v = Application.Caller.Parent.Index
which gives an 'object reqd' msg.

also
i = Application.Caller.Parent.Parent.Worksheets.Count
fails the same way.

I just did the latter with a copy/paste,
so it's not a typo problem.

I'm calling the macro from the spreadsheet with alt-F8.

Hope you can help,
Peter.

Peter

The code on that particular site is all Function code for User Defined
Functions.

These do not run as macros.

They act the same as regulae worksheet Functions in a cell.

You would copy the entire Function to a general module in your workbook then
call it from a cell.

e.g. copy the code below to a general module

Function SheetsCount() As Integer
Application.Volatile True
SheetsCount = Application.Caller.Parent.Parent.Worksheets.Count
End Function

Then in a worksheet cell enter

=SheetsCount() to return the number of sheets in the workbook.

Functions cannot copy things to other cells or sheets. They just return data
to the cell they are entered in.

Read Chip's page again and you will see he gives good examples of usage for
each of the UDF's on that page.

Gord Dibben Excel MVP


On Thu, 18 Nov 2004 17:58:28 -0500, "Peter Chatterton"
wrote:

I want to copy to the next workbook,
but some code I found doesn't even start to work.

At http://www.cpearson.com/excel/sheetref.htm
it talks about worksheets but I get stuck at:
Application.Volatile True
v = Application.Caller.Parent.Index
which gives an 'object reqd' msg.

also
i = Application.Caller.Parent.Parent.Worksheets.Count
fails the same way.

I just did the latter with a copy/paste,
so it's not a typo problem.

I'm calling the macro from the spreadsheet with alt-F8.

Hope you can help,
Peter.