Creating a summary sheet

N

nabanco

Hello,

I am using the below formula to try to create a summary sheet within one
workbook from multiple sheets. How would I change the below to look only at
column a-f and all rows in the worksheet that have actual information in the
row?

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name <> Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
Newsh.Cells(RwNum, 1).Value = Sh.Name

For Each myCell In Sh.Range("A1,D5:E5,Z10") '<--Change the
range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
 
N

nabanco

Hey Ron,

I tried using the "Copy a range/column after the last column with data" and
get a compile error when I run the macro. It says sub or function not
defined. It is highlighting the below code:
Last = LastCol(DestSh)
 
R

Ron de Bruin

Read this above the macro

Note: This example use the function LastCol

Copy the function also in you module

From the webpage

Where do I copy the macros and functions from this page?

1. Alt-F11
2. Insert>Module from the Menu bar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to run the subs

Common Functions required for all routines:

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
 
N

nabanco

Thanks Ron, that worked.

The "merge" sheet is taking the data from sheets 1,2 &3 and placing it
horizontally across the sheet. Is there a way to place the merged data
vertically, one sheet's data on top of the other ending at the last row of
data?

For example, my data is in columns A:F in three separate sheets. There may
be many rows completed in each sheet however. I am trying to create a
summary sheet that has A:F of all three sheets in one nice. Does this make
sense and is it possible?
 
N

nabanco

It's still doing the same thing, how do I get the merge sheet to show data
from all other sheets stacked on top of each other as opposed to next to each
other?
 
R

Ron de Bruin

If you use this example the data will be below each other and not next to each other

Try the example macro named "Test2" together with the LastRow function
 
N

nabanco

Thank you so much Ron for sticking with me to figure this out, it worked!
Thank you
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top