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

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" <(E-Mail Removed)> wrote in message news:FCD283CD-A6FE-4F40-903D-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message news:FCD283CD-A6FE-4F40-903D-(E-Mail Removed)...
> > 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
>

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


--

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


"nabanco" <(E-Mail Removed)> wrote in message news:271C9794-BF04-49A2-A250-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:FCD283CD-A6FE-4F40-903D-(E-Mail Removed)...
>> > 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
>>

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. 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
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "nabanco" <(E-Mail Removed)> wrote in message news:271C9794-BF04-49A2-A250-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message news:FCD283CD-A6FE-4F40-903D-(E-Mail Removed)...
> >> > 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
> >>
>

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" <(E-Mail Removed)> wrote in message news:8F903445-8090-4739-BDA9-(E-Mail Removed)...
> 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. 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
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "nabanco" <(E-Mail Removed)> wrote in message news:271C9794-BF04-49A2-A250-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message news:FCD283CD-A6FE-4F40-903D-(E-Mail Removed)...
>> >> > 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
>> >>
>>

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" <(E-Mail Removed)> wrote in message news:8F903445-8090-4739-BDA9-(E-Mail Removed)...
> > 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. 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
> >>
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl/tips.htm
> >>
> >>
> >> "nabanco" <(E-Mail Removed)> wrote in message news:271C9794-BF04-49A2-A250-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message news:FCD283CD-A6FE-4F40-903D-(E-Mail Removed)...
> >> >> > 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
> >> >>
> >>
>

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" <(E-Mail Removed)> wrote in message news:174D6815-4B9C-40D6-A417-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:8F903445-8090-4739-BDA9-(E-Mail Removed)...
>> > 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. 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
>> >>
>> >>
>> >> --
>> >>
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl/tips.htm
>> >>
>> >>
>> >> "nabanco" <(E-Mail Removed)> wrote in message news:271C9794-BF04-49A2-A250-(E-Mail Removed)...
>> >> > 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" <(E-Mail Removed)> wrote in message news:FCD283CD-A6FE-4F40-903D-(E-Mail Removed)...
>> >> >> > 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
>> >> >>
>> >>
>>

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" <(E-Mail Removed)> wrote in message news:174D6815-4B9C-40D6-A417-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message news:8F903445-8090-4739-BDA9-(E-Mail Removed)...
> >> > 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. 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
> >> >>
> >> >>
> >> >> --
> >> >>
> >> >> Regards Ron de Bruin
> >> >> http://www.rondebruin.nl/tips.htm
> >> >>
> >> >>
> >> >> "nabanco" <(E-Mail Removed)> wrote in message news:271C9794-BF04-49A2-A250-(E-Mail Removed)...
> >> >> > 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" <(E-Mail Removed)> wrote in message news:FCD283CD-A6FE-4F40-903D-(E-Mail Removed)...
> >> >> >> > 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
> >> >> >>
> >> >>
> >>
>

You are welcome

Thanks for the feedback

--

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


"nabanco" <(E-Mail Removed)> wrote in message news:5840D264-1EEE-4BC2-8442-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:174D6815-4B9C-40D6-A417-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message news:8F903445-8090-4739-BDA9-(E-Mail Removed)...
>> >> > 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. 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
>> >> >>
>> >> >>
>> >> >> --
>> >> >>
>> >> >> Regards Ron de Bruin
>> >> >> http://www.rondebruin.nl/tips.htm
>> >> >>
>> >> >>
>> >> >> "nabanco" <(E-Mail Removed)> wrote in message news:271C9794-BF04-49A2-A250-(E-Mail Removed)...
>> >> >> > 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" <(E-Mail Removed)> wrote in message
>> >> >> >> news:FCD283CD-A6FE-4F40-903D-(E-Mail Removed)...
>> >> >> >> > 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
>> >> >> >>
>> >> >>
>> >>
>>