how do I summarize worksheets into the first sheet for a mail merge please?

[raehippychick]

I have a workbook with about 6 worksheets with data in - these are on
for each department in the company

I am hoping to get these departmental worksheets to feed automaticall
into a summary sheet at the front that I can use as a data source for
mail merge

My problem is the feed sheets have totals in at the bottom of eac
block of data and also rows are added or removed periodically in thes
feed sheets so ordinary linking is not possible

These department tabs come from feeds directly from departmen
workbooks via links that are updated each time this main workbook i
opened - so if a person leaves or joins their info is added by th
department affected which is why the department tabs rows add an
vanish! All the columns in each tab are idential - it is just th
amount of rows that will change periodically

Is there a way of putting all *the data without the sum totals at th
bottom* into this first sheet and it *updating automatically to includ
when new rows in department tabs get added or removed*?

Many thank

 

[Bernie Deitrick]

Rae the hippy chick,

You can use a macro: the code below will create a "Summary Sheet" from
Sheet1 to Sheet6. The assumption is that there are headers in row 1 of each
sheet, the data starts in column A, and the formulas that you don't want to
include are not separated from the data block by a blank row. You will need
to update the sheet names used in the data transfer section to reflect your
actual sheet names.

If you have problems using a macro, post back.

HTH,
Bernie
MS Excel MVP

Sub UpdateSummarySheet()
Dim mySht As Worksheet
Dim myRange As Range

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary Sheet").Delete
Worksheets.Add.Name = "Summary Sheet"
Application.DisplayAlerts = True

'Data transfer summarization
With Sheets("Sheet1")
Set myRange = .Range("A1").CurrentRegion
myRange.Resize(myRange.Rows.Count - 1, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A1")
End With

For Each mySht In Sheets(Array("Sheet2", "Sheet3", _
"Sheet4", "Sheet5", "Sheet6"))
Set myRange = mySht.Range("A1").CurrentRegion
myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)
Next mySht
End Sub

 

[raehippychick]

Oh yes please - I could do with some help please!

I am new to macros and only just learning - I guess I have to adap
part of it to my spreadsheet?

I have copied it into the VB editor and am not sure what to do with i
now!

Many thanks

Rae

 

[raehippychick]

Hi

I think I am getting there now - any chance of a bit of help to fin
why the first row with the headings in doesn't come through when I ru
the macro please?

I have managed to make a button that runs it when pressed and I hav
been adding comments to the code each time I understand what anothe
bit does, so that I don't forgt or get confused when I look at it!

Rae

 

[raehippychick]

OK - I am nearly there!!! I have sussed the lack of heading row -
missed changing sheet1 to my first sheet name - doh!

Now my problem is all the sheets come through great except for th
second one which is pulling data from row32 - so it shows zeroes fo
that part - there are 30 rows of data in the first sheet plus th
heading row so it seems to have followed down from sheet one

The other sheets all start from the second row!

Any ideas please?

Thanks from a happy hippychick!

Rae

 

[Bernie Deitrick]

Rae,

I haven't been ignoring you: sometimes the real world intrudes - I had to go
buy a copy of "Return of the King" ;->

See my comments in-line.

HTH,
Bernie
MS Excel MVP

Now my problem is all the sheets come through great except for the
second one which is pulling data from row32

Assuming your second sheet is arranged identically like the others, and
there is a block of data starting in A1, I can't think of any reason why row
32 is coming through. Are you sure it is the data from row 32, and that rows
1 to 31 are filled in? If data from the second sheet is completely missing,
try checking your spelling of the sheet name in your code, and make sure
that you don't have any sapces at the end of the sheet name on the sheet
tab: the on error resume next will simply continue through those errors, so
that is a likely cause.

- so it shows zeroes for

that part

Are there zero values on your sheet? Or formulas that could return zero?

there are 30 rows of data in the first sheet plus the
heading row so it seems to have followed down from sheet one

The other sheets all start from the second row!

Do you want them to start from the first row? I didn't want to repeat
headings, which you don't want to do, left out to keep your new database
properly arranged.

Bernie

 

[raehippychick]

Bernie - thank you ever so much for all your help - sorry about all m
posts yesterday - I just got a bit over excited cos I was makin
progess and wanted to update when I solved the heading problem -
should have edited rather than reposted I think!

Today's situation is as below if I could have bit more help please?...

The HEAD01-03 sheets contain links to other workbooks but the HEAD04-0
do not - they have data in just text and numbers and a formula in on
column which copies across fine

All sheets will end up being links as I am updating this maste
spreadsheet to get data from other workbooks rather than the curren
situation which is to copy and past from one book to the other

When the summary page is created the sheets with links come throug
with zeroes but the sheets with text/numbers and one formula in on
column come through fine

When I look at the cells - the 'ordinary' sheet data is norma
text/numbers but the data from sheets with links is like this:

='T:\Rae\[HEAD01.xls]Sheet1'!A31
This shows the values as they are from the first sheet HEAD01 fine

='T:Rae\\[HEAD02.xls]Sheet1'!A32
This shows zeroes as values but the link in the formula bar - it shoul
be 'pulling through' from row 2 on sheet HEAD02

The link on HEAD02 that it should start at is cell A2 which contain
this in sheet HEAD02:-
='T:\Rae\[HEAD02.xls]Sheet1'!A2

All the files are in the same folder called 'Rae' so should I someho
take out the file path and just leave the file name? I have called th
workbooks the same as the worksheets tabs in the master - is that ok?

My altered code is:-

Sub UpdateSummarySheet()
Dim myths As Worksheet
Dim myRange As Range

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary Sheet").Delete
Worksheets.Add.Name = "Summary Sheet"
Application.DisplayAlerts = True

'Data transfer summarization
With Sheets("HEAD01")
Set myRange = .Range("A1").CurrentRegion
myRange.Resize(myRange.Rows.Count - 1, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A1")
End With

For Each mySht In Sheets(Array("HEAD02", _
"HEAD03", "HEAD04", "HEAD05", "HEAD06"))
Set myRange = mySht.Range("A1").CurrentRegion
myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)
Next mySht
End Sub

 

[Bernie Deitrick]

Rae,

Here's my attempt at making you an even happier hippy chick....

1) If you want your summary sheet to be links to the HEAD0? sheets, use the
first macro from below.

2) If you want the summary to include the same formulas (so that they are
linked to the original workbooks), then you will need to alter all of the
formulas on your HEAD0? sheets to include $s, like

='T:\Rae\[HEAD01.xls]Sheet1'!$A$31

Then your original macro will work fine.

3) If you want your summary to be values, use the second macro below.

HTH,
Bernie
MS Excel MVP

'Here's the first macro, to make links

Sub UpdateSummarySheetLinks()
Dim mySht As Worksheet
Dim myRange As Range

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary Sheet").Delete
Worksheets.Add.Name = "Summary Sheet"
Application.DisplayAlerts = True

'Data transfer summarization
With Sheets("HEAD01")
Set myRange = .Range("A1").CurrentRegion
myRange.Resize(myRange.Rows.Count - 1, _
myRange.Columns.Count).Copy
Worksheets("Summary Sheet").Range("A1").Select
ActiveSheet.Paste Link:=True
End With

For Each mySht In Sheets(Array("HEAD02", _
"HEAD03", "HEAD04", "HEAD05", "HEAD06"))
Set myRange = mySht.Range("A1").CurrentRegion
myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
myRange.Columns.Count).Copy
Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2).Select
ActiveSheet.Paste Link:=True
Next mySht
End Sub

'Here's the second macro

Sub UpdateSummarySheetValues()
Dim mySht As Worksheet
Dim myRange As Range

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary Sheet").Delete
Worksheets.Add.Name = "Summary Sheet"
Application.DisplayAlerts = True

'Data transfer summarization
With Sheets("HEAD01")
Set myRange = .Range("A1").CurrentRegion
myRange.Resize(myRange.Rows.Count - 1, _
myRange.Columns.Count).Copy
Worksheets("Summary Sheet").Range("A1").Select
Selection.PasteSpecial Paste:=xlValues
End With

For Each mySht In Sheets(Array("HEAD02", _
"HEAD03", "HEAD04")) ', "HEAD05", "HEAD06"))
Set myRange = mySht.Range("A1").CurrentRegion
myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
myRange.Columns.Count).Copy
Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2).Select
Selection.PasteSpecial Paste:=xlValues
Next mySht
End Sub

Bernie - thank you ever so much for all your help - sorry about all my
posts yesterday - I just got a bit over excited cos I was making
progess and wanted to update when I solved the heading problem - I
should have edited rather than reposted I think!

Today's situation is as below if I could have bit more help please?...

The HEAD01-03 sheets contain links to other workbooks but the HEAD04-06
do not - they have data in just text and numbers and a formula in one
column which copies across fine

All sheets will end up being links as I am updating this master
spreadsheet to get data from other workbooks rather than the current
situation which is to copy and past from one book to the other

When the summary page is created the sheets with links come through
with zeroes but the sheets with text/numbers and one formula in one
column come through fine

When I look at the cells - the 'ordinary' sheet data is normal
text/numbers but the data from sheets with links is like this:

='T:\Rae\[HEAD01.xls]Sheet1'!A31
This shows the values as they are from the first sheet HEAD01 fine

='T:Rae\\[HEAD02.xls]Sheet1'!A32
This shows zeroes as values but the link in the formula bar - it should
be 'pulling through' from row 2 on sheet HEAD02

The link on HEAD02 that it should start at is cell A2 which contains
this in sheet HEAD02:-
='T:\Rae\[HEAD02.xls]Sheet1'!A2

All the files are in the same folder called 'Rae' so should I somehow
take out the file path and just leave the file name? I have called the
workbooks the same as the worksheets tabs in the master - is that ok?

My altered code is:-

Sub UpdateSummarySheet()
Dim myths As Worksheet
Dim myRange As Range

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary Sheet").Delete
Worksheets.Add.Name = "Summary Sheet"
Application.DisplayAlerts = True

'Data transfer summarization
With Sheets("HEAD01")
Set myRange = .Range("A1").CurrentRegion
myRange.Resize(myRange.Rows.Count - 1, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A1")
End With

For Each mySht In Sheets(Array("HEAD02", _
"HEAD03", "HEAD04", "HEAD05", "HEAD06"))
Set myRange = mySht.Range("A1").CurrentRegion
myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)
Next mySht
End Sub

*Rae,

I haven't been ignoring you: sometimes the real world intrudes - I
had to go
buy a copy of "Return of the King" ;->

See my comments in-line.

HTH,
Bernie
MS Excel MVP



Assuming your second sheet is arranged identically like the others,
and
there is a block of data starting in A1, I can't think of any reason
why row
32 is coming through. Are you sure it is the data from row 32, and
that rows
1 to 31 are filled in? If data from the second sheet is completely
missing,
try checking your spelling of the sheet name in your code, and make
sure
that you don't have any sapces at the end of the sheet name on the
sheet
tab: the on error resume next will simply continue through those
errors, so
that is a likely cause.

- so it shows zeroes for

Are there zero values on your sheet? Or formulas that could return
zero?


Do you want them to start from the first row? I didn't want to
repeat
headings, which you don't want to do, left out to keep your new
database
properly arranged.

Bernie *

 

[Bernie Deitrick]

Oops, I was working on a short set of sheets for testing, so the line from
the second macro:

For Each mySht In Sheets(Array("HEAD02", _
"HEAD03", "HEAD04")) ', "HEAD05", "HEAD06"))

Should be edited to read

For Each mySht In Sheets(Array("HEAD02", _
"HEAD03", "HEAD04", "HEAD05", "HEAD06"))

Sorry about that,
Bernie
MS Excel MVP

 

[raehippychick]

Bernie - thank you so much - you are wonderful!

I figured the absolute referencing on the sheets out (I was so chuffe
with myself!!)

I shall have a go with second macro tomorrow

Thanks again

From the happiest hippychick

Rae

*Rae,

Here's my attempt at making you an even happier hippy chick....

1) If you want your summary sheet to be links to the HEAD0? sheets
use the
first macro from below.

2) If you want the summary to include the same formulas (so that the
are
linked to the original workbooks), then you will need to alter all o
the
formulas on your HEAD0? sheets to include $s, like

='T:\Rae\[HEAD01.xls]Sheet1'!$A$31

Then your original macro will work fine.

3) If you want your summary to be values, use the second macr
below.

HTH,
Bernie
MS Excel MVP