Ron,

This works really well with my workbook but I wanted it to be able to paste
the data from all worksheets into 1 column (as opposed to different columns
for each worksheet).

I tried the following which didn't work. Can you help me figure it out?

I changed this:
'Find the last Column with data on the DestSh
Last = LastCol(DestSh)

To this:
'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
Last = Last + 1

And changed this:
CopyRng.Copy
With DestSh.Cells(1, Last + 1)
.PasteSpecial 8 ' Column width
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

To this:
CopyRng.Copy
With DestSh.Cells(Last, 1)
.PasteSpecial 8 ' Column width
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

Thanks!



"Ron de Bruin" wrote:

Hi Bovine

See this page
http://www.rondebruin.nl/copy2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bovine Jones" wrote in message ...
I've got a series of spreadsheets that I need to do some work on but in order
to do it I need to consolidate all of the data into a single worksheet first.

What I'm looking for is something like a macro that that will look at my
workbook, read all of the sheets and then append the data from all of the
sheets into a new single worksheet - presumably as the first sheet of the
workbook but that's not important.

The good news is that all of the sheets will have the same number of columns
(from A:U), but the bad news is that the number of sheets could vary from
workbook to workbook (which isn't the end of the world because I can make
slight modifications to the macro to take into account the number of sheets -
but ideally I'd just like to have one macro that reads all of the sheets and
requires no changing as I might not be maintaining things in the longer term)
and of course the really bad news is that the number of rows of data will
vary wildly and will change from run to run as new records are added and old
ones are removed.

Is that a tough ask? An easy ask? An impossible ask?

Any help would be absolutely fantastically received from this doddery old
Bovine that needs some assistance desperately!

Thanks.