Creating a summary sheet

[Guest]

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

 

[Ron de Bruin]

If you want that use a Copy macro
See
http://www.rondebruin.nl/copy2.htm

 

[Guest]

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)

 

[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

 

[Guest]

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?

 

[Ron de Bruin]

Use this example on the page

Copy from row 2 till the last row with data

 

[Guest]

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?

 

[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

 

[Guest]

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

 

[Ron de Bruin]

You are welcome

Thanks for the feedback