I have same structure of data (same column headings) in different sheets.
Each sheet name is a cityname.
What is the easiest way to bring all data from all sheets to a single sheet?
In the new sheet I should also add a new column and put city name (sheet
name).
Is there any written macro in the Internet to do this job?
Some people may ask the reason that I want to merge already separeted data,
but I need to have data in a single sheet.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

collecting data from many sheets to one sheet

An easy way is to click on Data, Consolidate.


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=565914

Thanks. I haven't heard or used consolidate command. I checked the help.
sounds good and will help want I want to do. But like other commands, help is
not easy to follow at least for me. Is there any tutor in MVP for this
command?
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan


"raypayette" wrote:


An easy way is to click on Data, Consolidate.


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=565914

One more question about following link:
http://www.excelforum.com/showthread...hreadid=565914
I post my question to microsoft ng but it appears in above link as well.
Could you please explain about this procedure?
Is it automatic or is your setting? What is benefit. Generally what do you
call this technique in web?
will be happy to know more about above technique.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan


"raypayette" wrote:


An easy way is to click on Data, Consolidate.


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=565914

As stated:
"These forums are gateways to selected excel related usenet newsgroups.
"
More info:
http://www.newsadmin.com/bit/gatewayfaq.asp


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=565914

Ron de Bruin has code that will copy data from worksheets onto a master
sheet, and includes an option to insert the sheet name in a column.

http://www.rondebruin.nl/copy2.htm

Khoshravan wrote:
I have same structure of data (same column headings) in different sheets.
Each sheet name is a cityname.
What is the easiest way to bring all data from all sheets to a single sheet?
In the new sheet I should also add a new column and put city name (sheet
name).
Is there any written macro in the Internet to do this job?
Some people may ask the reason that I want to merge already separeted data,
but I need to have data in a single sheet.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I am trying to copy a macro with a function from www.rondebruin.nl/copy2.htm
site.
It has explained how to enter the MAcro but not mentioned how/where to
insert the function. how should I do this? sorry for simple question but it
is my first time use function command in excel.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

where/how to insert a function


"Debra Dalgleish" wrote:

Ron de Bruin has code that will copy data from worksheets onto a master
sheet, and includes an option to insert the sheet name in a column.

http://www.rondebruin.nl/copy2.htm

Khoshravan wrote:
I have same structure of data (same column headings) in different sheets.
Each sheet name is a cityname.
What is the easiest way to bring all data from all sheets to a single sheet?
In the new sheet I should also add a new column and put city name (sheet
name).
Is there any written macro in the Internet to do this job?
Some people may ask the reason that I want to merge already separeted data,
but I need to have data in a single sheet.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Rasoul

See
http://www.cpearson.com/excel/codemods.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Khoshravan" wrote in message ...
I am trying to copy a macro with a function from www.rondebruin.nl/copy2.htm
site.
It has explained how to enter the MAcro but not mentioned how/where to
insert the function. how should I do this? sorry for simple question but it
is my first time use function command in excel.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

where/how to insert a function


"Debra Dalgleish" wrote:

Ron de Bruin has code that will copy data from worksheets onto a master
sheet, and includes an option to insert the sheet name in a column.

http://www.rondebruin.nl/copy2.htm

Khoshravan wrote:
I have same structure of data (same column headings) in different sheets.
Each sheet name is a cityname.
What is the easiest way to bring all data from all sheets to a single sheet?
In the new sheet I should also add a new column and put city name (sheet
name).
Is there any written macro in the Internet to do this job?
Some people may ask the reason that I want to merge already separeted data,
but I need to have data in a single sheet.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Perhaps Ron will explain in the other thread, where you're working with
him on the same problem. ( Consolidate different worksheets into one
worksheet)

Khoshravan wrote:
I am trying to copy a macro with a function from www.rondebruin.nl/copy2.htm
site.
It has explained how to enter the MAcro but not mentioned how/where to
insert the function. how should I do this? sorry for simple question but it
is my first time use function command in excel.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thank you so much for this post!!!! It just saved my life and about a days
work! :-)

I modified the code to fit my needs, but it will not run. It stops at the
following line:
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy

Can someone tell me what I did wrong? Here's the modified code:
Sub MergeSheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

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

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

'Add a worksheet with the name "Master"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"

'loop through all worksheets and copy the data to the DestSh
For Each sh In Sheets(Array("Start", "End"))
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies values and formats from A2 onward
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False
Application.CutCopyMode = False
End With

'This will copy the sheet name in the H column if you want
'DestSh.Cells(Last + 1, "H").Value = sh.Name

Next

Application.Goto DestSh.Cells(1)

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

Thanks again!
--
Hile


"Ron de Bruin" wrote:

Hi Rasoul

See
http://www.cpearson.com/excel/codemods.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Khoshravan" wrote in message ...
I am trying to copy a macro with a function from www.rondebruin.nl/copy2.htm
site.
It has explained how to enter the MAcro but not mentioned how/where to
insert the function. how should I do this? sorry for simple question but it
is my first time use function command in excel.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

where/how to insert a function


"Debra Dalgleish" wrote:

Ron de Bruin has code that will copy data from worksheets onto a master
sheet, and includes an option to insert the sheet name in a column.

http://www.rondebruin.nl/copy2.htm

Khoshravan wrote:
I have same structure of data (same column headings) in different sheets.
Each sheet name is a cityname.
What is the easiest way to bring all data from all sheets to a single sheet?
In the new sheet I should also add a new column and put city name (sheet
name).
Is there any written macro in the Internet to do this job?
Some people may ask the reason that I want to merge already separeted data,
but I need to have data in a single sheet.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Add a dim line to the macro Hile

Dim shLast As Long


--

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


"Hile" wrote in message ...
Thank you so much for this post!!!! It just saved my life and about a days
work! :-)

I modified the code to fit my needs, but it will not run. It stops at the
following line:
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy

Can someone tell me what I did wrong? Here's the modified code:
Sub MergeSheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

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

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

'Add a worksheet with the name "Master"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"

'loop through all worksheets and copy the data to the DestSh
For Each sh In Sheets(Array("Start", "End"))
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies values and formats from A2 onward
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False
Application.CutCopyMode = False
End With

'This will copy the sheet name in the H column if you want
'DestSh.Cells(Last + 1, "H").Value = sh.Name

Next

Application.Goto DestSh.Cells(1)

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

Thanks again!
--
Hile


"Ron de Bruin" wrote:

Hi Rasoul

See
http://www.cpearson.com/excel/codemods.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Khoshravan" wrote in message ...
I am trying to copy a macro with a function from www.rondebruin.nl/copy2.htm
site.
It has explained how to enter the MAcro but not mentioned how/where to
insert the function. how should I do this? sorry for simple question but it
is my first time use function command in excel.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

where/how to insert a function


"Debra Dalgleish" wrote:

Ron de Bruin has code that will copy data from worksheets onto a master
sheet, and includes an option to insert the sheet name in a column.

http://www.rondebruin.nl/copy2.htm

Khoshravan wrote:
I have same structure of data (same column headings) in different sheets.
Each sheet name is a cityname.
What is the easiest way to bring all data from all sheets to a single sheet?
In the new sheet I should also add a new column and put city name (sheet
name).
Is there any written macro in the Internet to do this job?
Some people may ask the reason that I want to merge already separeted data,
but I need to have data in a single sheet.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Oops click send to fast

If there is no data on one of the sheets the you also have a problem

--

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


"Hile" wrote in message ...
Thank you so much for this post!!!! It just saved my life and about a days
work! :-)

I modified the code to fit my needs, but it will not run. It stops at the
following line:
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy

Can someone tell me what I did wrong? Here's the modified code:
Sub MergeSheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

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

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

'Add a worksheet with the name "Master"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"

'loop through all worksheets and copy the data to the DestSh
For Each sh In Sheets(Array("Start", "End"))
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies values and formats from A2 onward
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False
Application.CutCopyMode = False
End With

'This will copy the sheet name in the H column if you want
'DestSh.Cells(Last + 1, "H").Value = sh.Name

Next

Application.Goto DestSh.Cells(1)

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

Thanks again!
--
Hile


"Ron de Bruin" wrote:

Hi Rasoul

See
http://www.cpearson.com/excel/codemods.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Khoshravan" wrote in message ...
I am trying to copy a macro with a function from www.rondebruin.nl/copy2.htm
site.
It has explained how to enter the MAcro but not mentioned how/where to
insert the function. how should I do this? sorry for simple question but it
is my first time use function command in excel.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

where/how to insert a function


"Debra Dalgleish" wrote:

Ron de Bruin has code that will copy data from worksheets onto a master
sheet, and includes an option to insert the sheet name in a column.

http://www.rondebruin.nl/copy2.htm

Khoshravan wrote:
I have same structure of data (same column headings) in different sheets.
Each sheet name is a cityname.
What is the easiest way to bring all data from all sheets to a single sheet?
In the new sheet I should also add a new column and put city name (sheet
name).
Is there any written macro in the Internet to do this job?
Some people may ask the reason that I want to merge already separeted data,
but I need to have data in a single sheet.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

ahh
that must be the issue, i created a sandwich Start! and End! so that I
wouldn't have to keep updating the range because I want a certain number of
tabs that vary from month to month (Jan!, Feb!, etc.) but those aren't the
only tabs in the workbook so I didn't want to use the one that did ALL tabs.

The start and end tabs I inserted are empty. :-(
Here I thought I was being creative!

Thanks for the clarification.
--
Hile


"Ron de Bruin" wrote:

Oops click send to fast

If there is no data on one of the sheets the you also have a problem

--

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


"Hile" wrote in message ...
Thank you so much for this post!!!! It just saved my life and about a days
work! :-)

I modified the code to fit my needs, but it will not run. It stops at the
following line:
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy

Can someone tell me what I did wrong? Here's the modified code:
Sub MergeSheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

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

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

'Add a worksheet with the name "Master"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"

'loop through all worksheets and copy the data to the DestSh
For Each sh In Sheets(Array("Start", "End"))
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies values and formats from A2 onward
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues, , False, False
.PasteSpecial xlPasteFormats, , False, False
Application.CutCopyMode = False
End With

'This will copy the sheet name in the H column if you want
'DestSh.Cells(Last + 1, "H").Value = sh.Name

Next

Application.Goto DestSh.Cells(1)

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

Thanks again!
--
Hile


"Ron de Bruin" wrote:

Hi Rasoul

See
http://www.cpearson.com/excel/codemods.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Khoshravan" wrote in message ...
I am trying to copy a macro with a function from www.rondebruin.nl/copy2.htm
site.
It has explained how to enter the MAcro but not mentioned how/where to
insert the function. how should I do this? sorry for simple question but it
is my first time use function command in excel.
--
Rasoul Khoshravan Azar
Civil Engineer, Osaka, Japan

where/how to insert a function


"Debra Dalgleish" wrote:

Ron de Bruin has code that will copy data from worksheets onto a master
sheet, and includes an option to insert the sheet name in a column.

http://www.rondebruin.nl/copy2.htm

Khoshravan wrote:
I have same structure of data (same column headings) in different sheets.
Each sheet name is a cityname.
What is the easiest way to bring all data from all sheets to a single sheet?
In the new sheet I should also add a new column and put city name (sheet
name).
Is there any written macro in the Internet to do this job?
Some people may ask the reason that I want to merge already separeted data,
but I need to have data in a single sheet.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html