Creating a summary sheet
 

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

Creating a summary sheet
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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

Creating a summary sheet
 

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" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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

Creating a summary sheet
 

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. InsertModule 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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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

Creating a summary sheet
 

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" wrote:

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. InsertModule 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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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

Creating a summary sheet
 

Use this example on the page

Copy from row 2 till the last row with data





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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. InsertModule 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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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

Creating a summary sheet
 

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" wrote:

Use this example on the page

Copy from row 2 till the last row with data





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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. InsertModule 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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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

Creating a summary sheet
 

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

Use this example on the page

Copy from row 2 till the last row with data





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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. InsertModule 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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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

Creating a summary sheet
 

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

"Ron de Bruin" wrote:

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

Use this example on the page

Copy from row 2 till the last row with data





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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. InsertModule 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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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

Creating a summary sheet
 

You are welcome

Thanks for the feedback

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
Thank you so much Ron for sticking with me to figure this out, it worked!
Thank you

"Ron de Bruin" wrote:

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

Use this example on the page

Copy from row 2 till the last row with data





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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. InsertModule 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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message ...
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" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"nabanco" wrote in message
...
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