Creating an executive summary worksheet

[gkcarpenter]

I have a workbook that is 100 sheets long with 26 columns with varying
rows that update daily( currently as high as 500) . I am in the
process of creating a executive summary of the workbook on a separate
worksheet that needs the last cell in each worksheet to post to the
exec summary. Anyone have any ideas that might make this easier than
doing this manually every day

Gary

 

[Aqib Rizvi]

Gary
Name the last cell in each worksheet, and link the name in summary.
Aqib Rizvi

 

[Joel]

This code will work provide Column A has data in each worksheet. Crete a new
worksheet called "Summary" to put the data. I added in column A the sheet
name of the worksheet the row was copied from. You may want to comment these
lines out if you don't need them.


Sub summary()
SummaryRowCount = 1
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Summary" Then
Lastrow = sht.Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("summary")
sht.Rows(Lastrow).Copy Destination:= _
.Rows(SummaryRowCount)
'add sheet name to column A
.Range("A" & SummaryRowcount). _
Insert shift:=xlShiftToRight
.Range("A" & SummaryRowcount) = sht.Name
SummaryRowCount = SummaryRowCount + 1
End With
End If
Next sht

End Sub

 

[gkcarpenter]

This code will work provide Column A has data in each worksheet. Crete a new
worksheet called "Summary" to put the data. I added in column A the sheet
name of the worksheet the row was copied from. You may want to comment these
lines out if you don't need them.

Sub summary()
SummaryRowCount = 1
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Summary" Then
Lastrow = sht.Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("summary")
sht.Rows(Lastrow).Copy Destination:= _
.Rows(SummaryRowCount)
'add sheet name to column A
.Range("A" & SummaryRowcount). _
Insert shift:=xlShiftToRight
.Range("A" & SummaryRowcount) = sht.Name
SummaryRowCount = SummaryRowCount + 1
End With
End If
Next sht

End Sub






- Show quoted text -

Joel thanks for the help I am new at the programming within excel can
you give me a little more detail as to how I should proceed with doing
this. I appreciate your advice and I am sorry I am not more
experienced with this. I am really only pulling 2 cells from each
sheet and they are new each day will this accomplish this? thanks
again

Gary

 

[Joel]

It is hard sometimes telling how much experience a person has with
programming and VBA.

The code (program or macro what ever you want to call it) gets put into a
VBA module. This is accomplished using the VBA editor.

1) From worksheet menu - Tools - Macro - Visual Basic Editor
2) From VBA menu - Insert Module
3) Copy code from posting (starts with Sub summary() and ends with END SUB)
and paste into VBA module.
4) Check if code is inserted properly by compiling the code. Frm VBA menu
Debug - Compile VBA project
5) You need to check your security setting to make sure it is set to medium.
From Worksheet menu Tools - Macro - Security. Make sure Medium is set.
Medium will ask when workbook is opened if you want to enable macros. Enter
yes for this workbook
6) From worksheet menu, Insert Worksheet
7) Rename worksheet to summary, On bottom of new worksheet right click tab
(should say Sheet followed by a number) . Select Rename and type SUMMARY.
8) Run macro from worksheet menu. Tools - Macro - Macros - Summary.


The code will copy the entire last row from each worksheet. Doesn't matter
if you have two columns of data or 200 columns of data it will still work.
the only problem is if the last the last row of doesn't have data in column
A. Can change the code easily if the last row has data in another column.

The code doesn't clear the summary worksheet. You should clear the summary
worksheet before running macro. I can modify the code to automatically clear
the summary worksheet if necessary. I also don't know if you want to have
multiple summary worksheets so you can keep backups. The code checks every
sheet in the workbook, but skips the Summary worksheet when copying the last
row. I can modify the code to look for any worksheet starting with Summary
so you can have backups like Summary 10_01_07, Summary 10_02_07. Right now
the code skips the worksheet called summary and only summary.

 

[gkcarpenter]

It is hard sometimes telling how much experience a person has with
programming and VBA.

The code (program or macro what ever you want to call it) gets put into a
VBA module. This is accomplished using the VBA editor.

1) From worksheet menu - Tools - Macro - Visual Basic Editor
2) From VBA menu - Insert Module
3) Copy code from posting (starts with Sub summary() and ends with END SUB)
and paste into VBA module.
4) Check if code is inserted properly by compiling the code. Frm VBA menu
Debug - Compile VBA project
5) You need to check your security setting to make sure it is set to medium.
From Worksheet menu Tools - Macro - Security. Make sure Medium is set.
Medium will ask when workbook is opened if you want to enable macros. Enter
yes for this workbook
6) From worksheet menu, Insert Worksheet
7) Rename worksheet to summary, On bottom of new worksheet right click tab
(should say Sheet followed by a number) . Select Rename and type SUMMARY.
8) Run macro from worksheet menu. Tools - Macro - Macros - Summary.

The code will copy the entire last row from each worksheet. Doesn't matter
if you have two columns of data or 200 columns of data it will still work.
the only problem is if the last the last row of doesn't have data in column
A. Can change the code easily if the last row has data in another column.

The code doesn't clear the summary worksheet. You should clear the summary
worksheet before running macro. I can modify the code to automatically clear
the summary worksheet if necessary. I also don't know if you want to have
multiple summary worksheets so you can keep backups. The code checks every
sheet in the workbook, but skips the Summary worksheet when copying the last
row. I can modify the code to look for any worksheet starting with Summary
so you can have backups like Summary 10_01_07, Summary 10_02_07. Right now
the code skips the worksheet called summary and only summary.







- Show quoted text -

would it be easier to copy just the two Cells? in column C and colimn
U. As far as clearing the sheet that would be great if you would not
mind as far as back ups I safe each sheet as the new day prior to
updating

 

[Joel]

With you need in columns C and U it make sense to only copy these two values.
I made the appropriate changes. Paste the new code where the old code way
located. Your security mode will be the same if you are using the same PC.
Security setting only have to be checked once the first time you run a macro
from a particular PC.


Sub summary()
With Worksheets("summary")
.Cells.ClearContents
End With
SummaryRowCount = 1
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Summary" Then
LastRow = sht.Cells(Rows.Count, "C").End(xlUp).Row
With Worksheets("summary")
.Range("A" & SummaryRowCount) = sht.Name
.Range("B" & SummaryRowCount) = _
sht.Range("C" & LastRow)
.Range("C" & SummaryRowCount) = _
sht.Range("U" & LastRow)
SummaryRowCount = SummaryRowCount + 1
End With
End If
Next sht

End Sub

 

[marika]

It is hard sometimes telling how much experience a person has with
programming and VBA.

because they never ever respond, in words, on the net, just lurking
around
behind the scenes, trying to get people fired?

mk5000


"Thanks Malcolm I was wondering what to do over the holidays! Good
excuse
to get to the golf club and resurrect the "brains trust"-- Ken Hoyle

 

[gkcarpenter]

It is hard sometimes telling how much experience a person has with
programming and VBA.

The code (program or macro what ever you want to call it) gets put into a
VBA module. This is accomplished using the VBA editor.

1) From worksheet menu - Tools - Macro - Visual Basic Editor
2) From VBA menu - Insert Module
3) Copy code from posting (starts with Sub summary() and ends with END SUB)
and paste into VBA module.
4) Check if code is inserted properly by compiling the code. Frm VBA menu
Debug - Compile VBA project
5) You need to check your security setting to make sure it is set to medium.
From Worksheet menu Tools - Macro - Security. Make sure Medium is set.
Medium will ask when workbook is opened if you want to enable macros. Enter
yes for this workbook
6) From worksheet menu, Insert Worksheet
7) Rename worksheet to summary, On bottom of new worksheet right click tab
(should say Sheet followed by a number) . Select Rename and type SUMMARY.
8) Run macro from worksheet menu. Tools - Macro - Macros - Summary.

The code will copy the entire last row from each worksheet. Doesn't matter
if you have two columns of data or 200 columns of data it will still work.
the only problem is if the last the last row of doesn't have data in column
A. Can change the code easily if the last row has data in another column.

The code doesn't clear the summary worksheet. You should clear the summary
worksheet before running macro. I can modify the code to automatically clear
the summary worksheet if necessary. I also don't know if you want to have
multiple summary worksheets so you can keep backups. The code checks every
sheet in the workbook, but skips the Summary worksheet when copying the last
row. I can modify the code to look for any worksheet starting with Summary
so you can have backups like Summary 10_01_07, Summary 10_02_07. Right now
the code skips the worksheet called summary and only summary.







- Show quoted text -

OK joel I got it to work and it is truly amazing you just saved me
hours of work. I have renamed my executive summary and now have all of
my fields in the executive summary linked to "summary" Thanks for all
of your help
Gary

 

[Joel]

The code I gave you does not link the data, it just copies the data. the
code below has two minor changes and does the links.

Linking has an advantage that if you change any of the data in the workbook
the links will automatically update, there are advantages to both methios,
choose which ever you prefer

Sub summary()
With Worksheets("summary")
.Cells.ClearContents
End With
SummaryRowCount = 1
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Summary" Then
LastRow = sht.Cells(Rows.Count, "C").End(xlUp).Row
With Worksheets("summary")
.Range("A" & SummaryRowCount) = sht.Name
.Range("B" & SummaryRowCount) = _
"='" & sht.Name & "'!C" & LastRow
.Range("C" & SummaryRowCount) = _
"='" & sht.Name & "'!U" & LastRow
SummaryRowCount = SummaryRowCount + 1
End With
End If
Next sht

End Sub