Macro to move data from many tabs to a single one.

[Art MacNeil]

Hello all,

I have a spreadsheet with several hundred tabs. All these tabs have data
in Column A only. The data can be a in few rows or in one case, in 15,000
rows. There may be a solid line of data from Row 1 to the end of the tab or
it may have breaks. I started a macro where I named each tab and then the
range and then copied the data to a new spreadsheet. The Macro is getting to
be quite large and time consuming, I'm sure there is a faster/better way to
do this. Any ideas?

Thank you,

Art.

 

[Avenarius]

Hello Art,

do all your tabs having a special name or are they just named "Sheet1",
"Sheet2", and so on ?

Regards,
Avenarius

 

[Ron de Bruin]

Look here Art
http://www.rondebruin.nl/copy2.htm

Try
http://www.rondebruin.nl/copy2.htm#rows

Remember you only have 65536 rows

 

[Art MacNeil]

Hi Avenarius,

The tabs all have special names. They are not sheet1, sheet2, etc.

Thanks,

Art.

 

[Art MacNeil]

Hello Ron,

I went to the website but wasn't able to get any of the Macros there to
work for me. This is undoubtedly my inexperience and not due the Macro's
found there.

Thank you for replying,

Art.

 

[Art MacNeil]

Hi,

I decided to continue to do the Macro as I had started it. I named each
tab and then the range and then copied the data to a new spreadsheet by
naming each tab and copying the same code for each tab. The problem is when
I ran the Macro I got this compile error "procedure too large". The Macro
was originally 3,840 lines long. I broke it in 2 and it was still too
large. I broke it into 4 pieces and it finally worked.


Here is a sample of the Macro, it was run on a single spreadsheet 203 times.

Sheets("ABO1").Select '4th of 203 tabs.
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Windows("Combined dataxls").Activate
Range("F2").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = True
ActiveCell.Offset(0, 1).Select

Windows("Find Field ID's.xls").Activate
Range("A2").Select


The only portion that changed was the "Sheets("ABO1").Select" line.


Is it possible for this Macro to run without me copying the text above 200+
times?


Thank you for your consideration,

Art.

 

[Avenarius]

Hello Art,

only an additional idea if you want to work over your macro again.

Try just this one with a copy of your sheet:
ActiveSheet.Next.Select
ActiveSheet.Previous.Select
ActiveSheet.Next.Select
ActiveWindow.SelectedSheets.Delete

First count your sheets and insert a loop until your last sheet is
reached.

Fill inbetween your copy and paste functionality.

Cheers,
Avenarius

 

[Art MacNeil]

Hi Avenarius,

I'd like to learn how to do this better, in case i have to do something
like this again.

Am I right that the right format is:

Count the number of sheets, (WS_Count = ActiveWorkbook.Worksheets.Count)

Copy my Macro, (as written below)

Loop (not exactly sure how to do this, but I will look it up).

If my logic is correct, please let me know.

Thank you for your help,

Art.

 

[Avenarius]

Hello Art,

it is working as follow:
You can nest For...Next loops by placing one For...Next loop within
another. Give each loop a unique variable name as its counter. The
following construction is correct:

WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count

Your functionality


Next I

Cheers,
Avenarius