Stacking columns across multiple sheets

**mstinson23** · 12-19-2008, 01:49 PM

Hi,

I hope this question has not been answered. I did search and found some similar questions but could not get any of the solutions to fit my exact situation.

What I want to do is take column 'A' and column 'B' from several worksheets and stack them together in a new worksheet.

IE
Sheet1
A B
-----
1 A
2 A

Sheet2
A B
-----
3 B
4 B
5 B

Sheet 3
A B
-----
6 C
7 C

Stacked into

Sheet4
A B
-----
1 A
2 A
3 B
4 B
5 B
6 C
7 C

Is this possible? I currently just copy and paste it of course but that gets old as we update the information in each sheet quite often.

Thanks in advance!

Michael Stinson

**JBeaucaire** · 12-19-2008, 02:15 PM

This little macro will do it:

Sub Stack()
Application.ScreenUpdating = False
    Sheets("Sheet4").Select
    Range("A1:B1", Selection.End(xlDown)).ClearContents

'Copy Sheet1
    Sheets("Sheet1").Select
    Range("A1:B1", Selection.End(xlDown)).Copy
    Sheets("Sheet4").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'Copy Sheet2
    Sheets("Sheet2").Select
    Range("A1:B1", Selection.End(xlDown)).Copy
    Sheets("Sheet4").Select
    Columns("A:A").SpecialCells(xlCellTypeBlanks)(1, 1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'Copy Sheet3
    Sheets("Sheet3").Select
    Range("A1:B1", Selection.End(xlDown)).Copy
    Sheets("Sheet4").Select
    Columns("A:A").SpecialCells(xlCellTypeBlanks)(1, 1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
End Sub

**mstinson23** · 12-19-2008, 02:50 PM

Thanks!

This seems to bomb however when I hit sheet 3. I took out sheet 3 and it does exactly what I want with sheet 1 and 2. When I leave 3 in it gives me either a '400' if I run it from the macro window...or a 'Run-time error '1004': Application-defined or object-defined error' if I run it from the vb window.

Any ideas? The 3 worksheet example above is actually just an example...there are quite a number of tabs more that I really need to compile. Will this scale up to as many tabs as I need?

Michael Stinson

**JBeaucaire** · 12-19-2008, 05:56 PM

If you want me to troubleshoot your sheet, you'll have to post it. No idea why it won't work as designed since it does on mine...maybe a sheet naming error?

Also, yes, it will scale up as high as needed. Make sure you're using the last version I posted, the earliest version had a few lines of unneeded code.

**ravishankar** · 12-20-2008, 06:55 AM

Hi
you may try the macro in the attached file.
Ravi

**mstinson23** · 01-28-2009, 03:08 PM

Ravi,

I thought we had solved this a different way so I hadn't been back...but I just discovered I still needed this and found your macro. It works great...thanks!

Michael