Hi BJ

Maybe you have some other code running within the workbook, as I don't
experience what you are saying.

Try putting
Application.EnableEvents = False
on the line before
Application.ScreenUpdating = False
..
..
and then
..
Application.EnableEvents = True
after
Application.ScreenUpdating = True

--
Regards

Roger Govier


"Bovine Jones" wrote in message
...
Roger

This is almost, so close to being perfect... but for some reason it
flashes
up various save file screens while it's doing it that need responses
to
(which in every case has been cancel.)

Once it's done that it works fantastically.

Any suggestions?

Thanks.

BJ.

"Roger Govier" wrote:

Hi

The following code will look for a sheet called Summary.
If there is any data on the sheet, it will delete everything from
row2
down to the end of the sheet, preserving the headers in row 1.
It will then cycle though all sheets in the workbook, adding data
from
row 2 to the end of data on that sheet between columns A and U, and
append it to data on the Summary sheet.

The procedure assumes that column A will always have data in it for
each
row required, and this is the column that is used for counting the
number of used rows on each sheet. If that is not the case, then you
will need to amend the column number according in the lines
containing

Cells(Rows.Count, 1).End(xlUp).Row - change the 1 for column A to
the
number of the column to be used for the count.



Sub ColateData()

Dim Dsheet As Worksheet, Ssheet As Worksheet
Dim addrow As Long, lastrow As Long
Dim source As Range, dest As Range

Application.ScreenUpdating = False
ThisWorkbook.Sheets("Summary").Select
Set Ssheet = ThisWorkbook.Sheets("Summary")
With Ssheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
If lastrow = 1 Then lastrow = 2
Rows("2:" & lastrow).Clear
End With

For Each Dsheet In Worksheets
With Ssheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
End With

If Dsheet.Name < "Summary" Then
addrow = Dsheet.Cells(Rows.Count, 1).End(xlUp).Row
Set source = Dsheet.Range("A2:U" & addrow)
Set dest = Ssheet.Range("A" & lastrow + 1)
source.Copy dest
End If
Next

Application.ScreenUpdating = True
End Sub


You can copy the code and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your file.

To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert Module
Paste code in Module

For more information on adding code to a Workbook then David
McRitchie
has lots of useful help on his site at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm
--
Regards

Roger Govier


"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.

Roger

Cracked it! It's because the macro when it's run is looking at the
spreadsheet and sees that it is referencing links to spreadsheets that don't
exist. Break the links and it works perfectly. Many many thanks.

Now just one final question... is it possible so that when I paste it's
pasted as values only (without the formatting?)

Then I promise I'll leave you alone!

Hi BJ

One way

After the Next statement, and before Application.ScreenUpdating = True,
and the following

With Ssheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:U" & lastrow).Value = Range("A2:U" & lastrow).Value
End With

--
Regards

Roger Govier


"Bovine Jones" wrote in message
...
Roger

Cracked it! It's because the macro when it's run is looking at the
spreadsheet and sees that it is referencing links to spreadsheets that
don't
exist. Break the links and it works perfectly. Many many thanks.

Now just one final question... is it possible so that when I paste
it's
pasted as values only (without the formatting?)

Then I promise I'll leave you alone!

That is fantastic. Thanks Roger.

"Roger Govier" wrote:

Hi BJ

One way

After the Next statement, and before Application.ScreenUpdating = True,
and the following

With Ssheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:U" & lastrow).Value = Range("A2:U" & lastrow).Value
End With

--
Regards

Roger Govier


"Bovine Jones" wrote in message
...
Roger

Cracked it! It's because the macro when it's run is looking at the
spreadsheet and sees that it is referencing links to spreadsheets that
don't
exist. Break the links and it works perfectly. Many many thanks.

Now just one final question... is it possible so that when I paste
it's
pasted as values only (without the formatting?)

Then I promise I'll leave you alone!