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.