Windows Excel to Mac Excel Macro Problem

[fergalom]

Hi,

I have created a XL spreadsheet in excel on winxp where i copy in some text
data and the spreadsheet then extracts certain data from the text and I then
use a macro to Organise it.

It turn out I now have to use the spreadsheet on a mac. Spreadsheet opens and
functions correctly but when I try to run the macro, macXL throws up a debug
error on the first line of the code. (The spreadsheet works perfectly on
winXL)

There are no API calls in the code and also I call the macros on MacXL via a
shortcut key.

Here are the first couple of lines of the code. MacXL returns a debug error
on the first line.

Sub BBB1()
'
' BBB1 Macro
' Macro recorded 23/09/2007 by -
'

'
Sheets("E-mail data").Select
Range("A20:M20").Select
Selection.Copy
Sheets("Registrations").Select
Range("b3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("b3").Copy
Range("o3").Select

Hope you can help.
F

 

[JE McGimpsey]

Here are the first couple of lines of the code. MacXL returns a debug error
on the first line.

Sub BBB1()
'
' BBB1 Macro
' Macro recorded 23/09/2007 by -
'

'
Sheets("E-mail data").Select
Range("A20:M20").Select
Selection.Copy
Sheets("Registrations").Select
Range("b3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

What actual error do you get? ("debug" is not an error, it's an option)

The most likely error for the first line is a "Subscript out of range"
error if you don't have a worksheet in the Active Workbook named "E-mail
data".

Note that you can replace the above without the selections using
something like:

Dim rSource As Range
Dim rDest As Range

Set rSource = Sheets("E-mail data").Range("A20:M20")
Set rDest = Sheets("Registration").Range("B3").Resize( _
rSource.Rows.Count, rSource.Columns.Count)
rDest.Value = rSource.Value


Or, more compactly with

With Sheets("E-mail data").Range("A20:M20")
Sheets("Registration").Range("B3").Resize( _
.Rows.Count, .Columns.Count).Value = .Value
End With

 

[fergalom]

Thanks for the info,

The spreadsheet does contain a sheet called "E-mail data" (i.e. word for word
and case sensitive match)

The exact error is:

"Runtime error 32809 - Application defined or object defined error"

Here are the first couple of lines of the code. MacXL returns a debug error
on the first line.

[quoted text clipped - 14 lines]

SkipBlanks _
:=False, Transpose:=False

What actual error do you get? ("debug" is not an error, it's an option)

The most likely error for the first line is a "Subscript out of range"
error if you don't have a worksheet in the Active Workbook named "E-mail
data".

Note that you can replace the above without the selections using
something like:

Dim rSource As Range
Dim rDest As Range

Set rSource = Sheets("E-mail data").Range("A20:M20")
Set rDest = Sheets("Registration").Range("B3").Resize( _
rSource.Rows.Count, rSource.Columns.Count)
rDest.Value = rSource.Value

Or, more compactly with

With Sheets("E-mail data").Range("A20:M20")
Sheets("Registration").Range("B3").Resize( _
.Rows.Count, .Columns.Count).Value = .Value
End With

 

[JE McGimpsey]

The spreadsheet does contain a sheet called "E-mail data" (i.e. word for word
and case sensitive match)

The exact error is:

"Runtime error 32809 - Application defined or object defined error"

The only reference I can find to that particular error relates to a
missing type library. In the VBE, choose Tools/References, and see if
any of the type libraries start with "MISSING". If so, you'll have to
reinstall them.

 

[fergalom via MacKB.com]

Thanks for the help.

Sorted it myself in the end. Turns out, I had a rogue winXL control button on
the spreadsheet. Must have creeped in while the spreadsheet was being put
together.

Once I deleted that, it worked away no problem in MacXL

F

 

[JE McGimpsey]

Sorted it myself in the end. Turns out, I had a rogue winXL control button on
the spreadsheet. Must have creeped in while the spreadsheet was being put
together.

Once I deleted that, it worked away no problem in MacXL

Thanks for the info!