|
collecting data from many sheets to one sheet
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 |
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 |
collecting data from many sheets to one sheet
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 |
collecting data from many sheets to one sheet
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 |
collecting data from many sheets to one sheet
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 |
collecting data from many sheets to one sheet
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 |
collecting data from many sheets to one sheet
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 |
collecting data from many sheets to one sheet
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 |
collecting data from many sheets to one sheet
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 |
collecting data from many sheets to one sheet
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 |
collecting data from many sheets to one sheet
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 |
collecting data from many sheets to one sheet
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 |
collecting data from many sheets to one sheet
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 |
| All times are GMT +1. The time now is 06:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com