Merging Worksheets together into Master Worksheet
 

I am trying to merge all worksheets from the workbook into one master sheet.
I have downloaded Ron's example from his webpage and it works great in his
test file. But when I close that file and bring up the workbook that I want
to run the macro in, it has disappeared. How do I get the macro into my
personal file, so that I can use in whenever I want?

thanks bunches, I am very new to this.
marcia

Merging Worksheets together into Master Worksheet
 

Hi marcia2026

For others this is the code page
http://www.rondebruin.nl/copy2.htm

If you want to use the code in all your workbooks you can copy it in your personal.xls(b)

1) Select the PERSONAL.XLS(B) in the project window and click on the + before it
2) Then click on the + before Modules
3) Double click on Module1 to open the code window on the right
4) We copy/paste the macro and function in this module.
5) Now use Ctrl-s or FileSave in the VBA editor to save the PERSONAL.XLS.
6) We use Alt€“q to go back to Excel or use "File Close and Return to Microsoft Excel".
7) You can run the macro then with Alt-F8 or ToolsMacro€¦Macros / Developer tabMacros

See also if you not have this file
http://www.rondebruin.nl/personal.htm






--

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


"marcia2026" wrote in message ...
I am trying to merge all worksheets from the workbook into one master sheet.
I have downloaded Ron's example from his webpage and it works great in his
test file. But when I close that file and bring up the workbook that I want
to run the macro in, it has disappeared. How do I get the macro into my
personal file, so that I can use in whenever I want?

thanks bunches, I am very new to this.
marcia

Merging Worksheets together into Master Worksheet
 

Ron,
Please excuse the simplistic questions, but I am extremely new at this.

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other? Or do I
create separate files like you did? If so, how.

thanks so much.
marcia

"marcia2026" wrote:

I am trying to merge all worksheets from the workbook into one master sheet.
I have downloaded Ron's example from his webpage and it works great in his
test file. But when I close that file and bring up the workbook that I want
to run the macro in, it has disappeared. How do I get the macro into my
personal file, so that I can use in whenever I want?

thanks bunches, I am very new to this.
marcia

Merging Worksheets together into Master Worksheet
 

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other?

Yes both in the same module
Not important which one is on top


--

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


"marcia2026" wrote in message ...
Ron,
Please excuse the simplistic questions, but I am extremely new at this.

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other? Or do I
create separate files like you did? If so, how.

thanks so much.
marcia

"marcia2026" wrote:

I am trying to merge all worksheets from the workbook into one master sheet.
I have downloaded Ron's example from his webpage and it works great in his
test file. But when I close that file and bring up the workbook that I want
to run the macro in, it has disappeared. How do I get the macro into my
personal file, so that I can use in whenever I want?

thanks bunches, I am very new to this.
marcia

Merging Worksheets together into Master Worksheet
 

"Ron de Bruin" wrote:

Hi marcia2026

For others this is the code page
http://www.rondebruin.nl/copy2.htm

If you want to use the code in all your workbooks you can copy it in your personal.xls(b)

1) Select the PERSONAL.XLS(B) in the project window and click on the + before it
2) Then click on the + before Modules
3) Double click on Module1 to open the code window on the right
4) We copy/paste the macro and function in this module.
5) Now use Ctrl-s or FileSave in the VBA editor to save the PERSONAL.XLS.
6) We use Alt€“q to go back to Excel or use "File Close and Return to Microsoft Excel".
7) You can run the macro then with Alt-F8 or ToolsMacro€¦Macros / Developer tabMacros

See also if you not have this file
http://www.rondebruin.nl/personal.htm






--

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


"marcia2026" wrote in message ...
I am trying to merge all worksheets from the workbook into one master sheet.
I have downloaded Ron's example from his webpage and it works great in his
test file. But when I close that file and bring up the workbook that I want
to run the macro in, it has disappeared. How do I get the macro into my
personal file, so that I can use in whenever I want?

thanks bunches, I am very new to this.
marcia

Merging Worksheets together into Master Worksheet
 

Thanks,
works very well now. But I have tw more questions. I was actually able to
make the changes to print just the rows on each sheet that I need. But how
do I get it to print the same headings at the top of the merge sheet and how
do I get it to print just the visable sheets? There are two sheets that I
don't want it to copy and I thought that if I could get it to print just the
visable sheets that I could hide those two.

thanks
marcia

"Ron de Bruin" wrote:

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other?

Yes both in the same module
Not important which one is on top


--

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


"marcia2026" wrote in message ...
Ron,
Please excuse the simplistic questions, but I am extremely new at this.

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other? Or do I
create separate files like you did? If so, how.

thanks so much.
marcia

"marcia2026" wrote:

I am trying to merge all worksheets from the workbook into one master sheet.
I have downloaded Ron's example from his webpage and it works great in his
test file. But when I close that file and bring up the workbook that I want
to run the macro in, it has disappeared. How do I get the macro into my
personal file, so that I can use in whenever I want?

thanks bunches, I am very new to this.
marcia

Merging Worksheets together into Master Worksheet
 

Hi marcia

Show me the code that you use now



--

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


"marcia2026" wrote in message ...
Thanks,
works very well now. But I have tw more questions. I was actually able to
make the changes to print just the rows on each sheet that I need. But how
do I get it to print the same headings at the top of the merge sheet and how
do I get it to print just the visable sheets? There are two sheets that I
don't want it to copy and I thought that if I could get it to print just the
visable sheets that I could hide those two.

thanks
marcia

"Ron de Bruin" wrote:

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other?

Yes both in the same module
Not important which one is on top


--

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


"marcia2026" wrote in message ...
Ron,
Please excuse the simplistic questions, but I am extremely new at this.

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other? Or do I
create separate files like you did? If so, how.

thanks so much.
marcia

"marcia2026" wrote:

I am trying to merge all worksheets from the workbook into one master sheet.
I have downloaded Ron's example from his webpage and it works great in his
test file. But when I close that file and bring up the workbook that I want
to run the macro in, it has disappeared. How do I get the macro into my
personal file, so that I can use in whenever I want?

thanks bunches, I am very new to this.
marcia

Merging Worksheets together into Master Worksheet
 

Note: This example use the function LastRow
'This example copy the range A2:G2 from each worksheet.
'
'Change the range here
'
''Fill in the range that you want to copy
'Set CopyRng = sh.Range("A2:G2")

'When you run one of the examples it will first delete the summary worksheet
'named RDBMergeSheet if it exists and then adds a new one to the workbook.
'This ensures that the data is always up to date after you run the code.

'*****READ THE TIPS on the website****

Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "Total" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Total").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Total"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Total"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets

'Loop through all worksheets execpt the Total worksheet and the
'Information worksheet, you can ad more sheets to the array if you
want.
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Information"), 0))
Then

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.Rows("11:58")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'copy the values
With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count,
..Columns.Count).Value = .Value
End With

End If
Next

ExitTheSub:

Application.GoTo DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Thanks so very much for ALL of your help. I really appreciate it.
"Ron de Bruin" wrote:

Hi marcia

Show me the code that you use now



--

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


"marcia2026" wrote in message ...
Thanks,
works very well now. But I have tw more questions. I was actually able to
make the changes to print just the rows on each sheet that I need. But how
do I get it to print the same headings at the top of the merge sheet and how
do I get it to print just the visable sheets? There are two sheets that I
don't want it to copy and I thought that if I could get it to print just the
visable sheets that I could hide those two.

thanks
marcia

"Ron de Bruin" wrote:

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other?

Yes both in the same module
Not important which one is on top


--

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


"marcia2026" wrote in message ...
Ron,
Please excuse the simplistic questions, but I am extremely new at this.

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other? Or do I
create separate files like you did? If so, how.

thanks so much.
marcia

"marcia2026" wrote:

I am trying to merge all worksheets from the workbook into one master sheet.
I have downloaded Ron's example from his webpage and it works great in his
test file. But when I close that file and bring up the workbook that I want
to run the macro in, it has disappeared. How do I get the macro into my
personal file, so that I can use in whenever I want?

thanks bunches, I am very new to this.
marcia

Merging Worksheets together into Master Worksheet
 

I will look at it tomorrow

Bed time for me now



--

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


"marcia2026" wrote in message ...
Note: This example use the function LastRow
'This example copy the range A2:G2 from each worksheet.
'
'Change the range here
'
''Fill in the range that you want to copy
'Set CopyRng = sh.Range("A2:G2")

'When you run one of the examples it will first delete the summary worksheet
'named RDBMergeSheet if it exists and then adds a new one to the workbook.
'This ensures that the data is always up to date after you run the code.

'*****READ THE TIPS on the website****

Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "Total" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Total").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Total"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Total"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets

'Loop through all worksheets execpt the Total worksheet and the
'Information worksheet, you can ad more sheets to the array if you
want.
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Information"), 0))
Then

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.Rows("11:58")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'copy the values
With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count,
.Columns.Count).Value = .Value
End With

End If
Next

ExitTheSub:

Application.GoTo DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Thanks so very much for ALL of your help. I really appreciate it.
"Ron de Bruin" wrote:

Hi marcia

Show me the code that you use now



--

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


"marcia2026" wrote in message ...
Thanks,
works very well now. But I have tw more questions. I was actually able to
make the changes to print just the rows on each sheet that I need. But how
do I get it to print the same headings at the top of the merge sheet and how
do I get it to print just the visable sheets? There are two sheets that I
don't want it to copy and I thought that if I could get it to print just the
visable sheets that I could hide those two.

thanks
marcia

"Ron de Bruin" wrote:

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other?

Yes both in the same module
Not important which one is on top


--

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


"marcia2026" wrote in message
...
Ron,
Please excuse the simplistic questions, but I am extremely new at this.

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other? Or do I
create separate files like you did? If so, how.

thanks so much.
marcia

"marcia2026" wrote:

I am trying to merge all worksheets from the workbook into one master sheet.
I have downloaded Ron's example from his webpage and it works great in his
test file. But when I close that file and bring up the workbook that I want
to run the macro in, it has disappeared. How do I get the macro into my
personal file, so that I can use in whenever I want?

thanks bunches, I am very new to this.
marcia

Merging Worksheets together into Master Worksheet
 

Try this

There are two sheets that I don't want it to copy

Add the two sheet names in the array (replace sheet1 and sheet2)

If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Information", "Sheet1", "Sheet2"), 0)) Then


This eample copy the first row of the first sheet as header row


Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "Total" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Total").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Total"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Total"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets

'Loop through all worksheets execpt the Total worksheet and the
'Information worksheet, you can ad more sheets to the array if you want.
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Information", "Sheet1", "Sheet2"), 0)) Then


'Copy the header row one time
If DestSh.Range("A1") = "" Then
sh.Rows(1).Copy DestSh.Range("A1")
End If

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

'Fill in the range that you want to copy
'Set CopyRng = sh.Rows("11:58")
Set CopyRng = sh.Rows("2:5")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'copy the values
With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

End If
Next

ExitTheSub:

Application.GoTo DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


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




--

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


"Ron de Bruin" wrote in message ...
I will look at it tomorrow

Bed time for me now



--

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


"marcia2026" wrote in message ...
Note: This example use the function LastRow
'This example copy the range A2:G2 from each worksheet.
'
'Change the range here
'
''Fill in the range that you want to copy
'Set CopyRng = sh.Range("A2:G2")

'When you run one of the examples it will first delete the summary worksheet
'named RDBMergeSheet if it exists and then adds a new one to the workbook.
'This ensures that the data is always up to date after you run the code.

'*****READ THE TIPS on the website****

Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "Total" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Total").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Total"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Total"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets

'Loop through all worksheets execpt the Total worksheet and the
'Information worksheet, you can ad more sheets to the array if you
want.
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Information"), 0))
Then

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.Rows("11:58")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'copy the values
With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count,
.Columns.Count).Value = .Value
End With

End If
Next

ExitTheSub:

Application.GoTo DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Thanks so very much for ALL of your help. I really appreciate it.
"Ron de Bruin" wrote:

Hi marcia

Show me the code that you use now



--

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


"marcia2026" wrote in message ...
Thanks,
works very well now. But I have tw more questions. I was actually able to
make the changes to print just the rows on each sheet that I need. But how
do I get it to print the same headings at the top of the merge sheet and how
do I get it to print just the visable sheets? There are two sheets that I
don't want it to copy and I thought that if I could get it to print just the
visable sheets that I could hide those two.

thanks
marcia

"Ron de Bruin" wrote:

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other?

Yes both in the same module
Not important which one is on top


--

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


"marcia2026" wrote in message
...
Ron,
Please excuse the simplistic questions, but I am extremely new at this.

When I get to statement 4, do I paste the macro in example 1 and the
functions in the same window? Which one should follow the other? Or do I
create separate files like you did? If so, how.

thanks so much.
marcia

"marcia2026" wrote:

I am trying to merge all worksheets from the workbook into one master sheet.
I have downloaded Ron's example from his webpage and it works great in his
test file. But when I close that file and bring up the workbook that I want
to run the macro in, it has disappeared. How do I get the macro into my
personal file, so that I can use in whenever I want?

thanks bunches, I am very new to this.
marcia