Copying Data Range from Multiple Sheets

**UncleSalty** · 05-13-2011, 07:15 PM

Hello folks. I'm posting my question here after many hours of tinkering and playing around on my own. I'm a tenderfoot with VBAs and macros and I don't know programming language that well. I hope someone can help me out with this one.

I have a workbook that contains about 30-35 sheets and I need to copy a data range from each one of these individual sheets and paste it into either a new sheet or a new workbook, doesn't matter. The data range is W13:AD20 (no header row--values and formats). I really only need W13:W20 and AC13:AD20, but for the sake of simplicity I would be happy just to have the whole range copied out.

I tried using this code here, altered to fit my circumstances, but it wasn't working for me. I kept getting a "Last Row" error.

Any help would be appreciated much. I am using Excel 2004 for Mac if that helps. Thanks!

**jeffreybrown** · 05-13-2011, 07:45 PM

Hello & Welcome to the Board,

First of all let me say I know nothing about Excel for MAC, but the code you are trying should work fine.

With that error, is it possible you forgot to copy

Function LastRow
Function LastCol

It's at the top of the page under overview.

**UncleSalty** · 05-13-2011, 11:18 PM

Okay, thanks for the reply. I will try that out and see how it goes. Like I said, I'm not that sharp on VBA and I didn't put that LastRow function in. Hopefully this will work out. Cheers.

**jeffreybrown** · 05-13-2011, 11:25 PM

Take a look here and you will see Ron has a sample workbook you can download. If you download the workbook and then open the VBE you can step through the code using F8 and watch what is happening so it might become clearer.

**UncleSalty** · 05-14-2011, 02:32 AM

Yup, adding that module for the LastRow stuff made it work perfectly! Thanks for the help, brother!

**Richard Buttrey** · 05-14-2011, 07:51 AM

Hi,

...and as general piece of information for the future please note that there are several instances where the VBA syntax for Excel for Windows, (which of course is mostly what you'll see hereabouts), is different to the VBA syntax that the Mac uses.

Apart from trivial stuff like the path separator being "\" instead of ":", there are other more fundamental differences. e.g. there is no .RowSource property on the Mac and I have found on occasions that trying to add a macro to a sheet control doesn't work. Something like a simple drawing rectangle will accept a macro call but other sheet objects won't.

And it goes without saying that when you upgrade you'll need to avoid Excel for Mac 2008 since VBA isn;t supported at all. You'd need to go straight to Excel for Mac 2011 - and then presumably discover all the strange idiosyncracyies to be found there.

Regards

**jeffreybrown** · 05-14-2011, 10:11 AM

@ UncleSalty...you're welcome and glad it worked for you

If you are satisfied with the answer provided, please don’t forget to mark the thread as solved.

How to mark a thread Solved
Go to the first post
Select
-- Edit
-- Go Advanced
-- Below the word Title you will see a dropdown with the word No prefix
-- Update to Solved and then select Save

@ Richard...thanks for pointing out some of the differences