VBA PC-Mac Compatibility

[MikeM]

I'm writing VBA code to be used on both PC & Mac, using Excel 2003 SP1 for
the PC version.

Everything works but turning off screenupdating on the Mac. I copy ranges
from 3 sheets in 1 workbook and paste them into another workbook, and I don't
want the users to see this, but Application.ScreenUpdating doesnt work.

Alternatively, I tried to copy without actually selecting the workbook, with
a command:

workbook(A),sheets(B).range(C).copy

instead of

workbook(A).select
sheets(B)range(C).copy

but I can't do that (why?), so the Mac user keeps seeing the back-and-forth
copying.

 

[JE McGimpsey]

Alternatively, I tried to copy without actually selecting the workbook, with
a command:

workbook(A),sheets(B).range(C).copy

instead of

workbook(A).select
sheets(B)range(C).copy

but I can't do that (why?), so the Mac user keeps seeing the back-and-forth
copying.

The proper syntax is

Workbook(A).Sheets(B).Range(C).Copy Destination:= _
Workbook(D).Sheets(E).Range(F)

Note: you should almost never have to select anything. Using range
objects directly makes your code smaller, faster, and IMO, easier to
maintain.

 

[Bernard Rey]

MikeM wrote :

Everything works but turning off screenupdating on the Mac. I copy ranges
from 3 sheets in 1 workbook and paste them into another workbook, and I don't
want the users to see this, but Application.ScreenUpdating doesnt work.

Which version/update of Excel are you using on the Mac side? It should (and
usually does) work on the Mac.

Alternatively, I tried to copy without actually selecting the workbook, with
a command:

workbook(A),sheets(B).range(C).copy

instead of

workbook(A).select
sheets(B)range(C).copy

but I can't do that (why?), so the Mac user keeps seeing the back-and-forth
copying.

In addition to JE McGimpsey's indication I could add I guess you mistyped
the instruction in you message (missing "s").

The following set runs (should run) fine:


Application.ScreenUpdating = False

Workbooks(A).Sheets(B).Range(C).Copy Destination:= _
Workbooks(D).Sheets(E).Range(F)

 

[JE McGimpsey]

Of course, that's not correct. Should be "WorkbookS"

 

[MikeM]

Thanks, Messrs. McGimpsey & Rey,

But will it work with a transpose operation as well?

Mike Maltz

 

[Bernard Rey]

MikeM :

But will it work with a transpose operation as well?

If you mean "Will it run on the Windows side too?", the answer is "Yes" ;-)

If "transpose" means something else, please be more specific...

 

[MikeM]

Thanks, Bernard. "Transpose" is one of the options under "paste special." One
can copy, e.g., A1:A5 and transpose it and past it in A10:E10.

 

[Bernard Rey]

I see.

Then you'd write it:

Application.ScreenUpdating = False

Workbooks(A).Sheets(B).Range(C).Copy
Workbooks(D).Sheets(E).Range(F).PasteSpecial Paste:=xlAll, _
Transpose:=True

Note that the "Paste:=xlAll" statement is optional, I left it just in case.
It can also be changed to "Paste:=xlPasteValue" or so, depending upon what
you intend to do...

HTH


--
Bernard Rey - Toulouse / France


MikeM :