Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Sorting data in a workbook across multple worksheets
I have a workbook with six worksheets. Data (questions)in each worksheet are
arranged in rows, and one column is a reference number for each question. I would like to know if it is possible to sort all the tabs together using that reference number. I assume the results would have to be produced on another tab, I can't think if there is another way to do it. I know I can sort each tab individually, I am looking to sort the data in entire workbook at once across all tabs. Appreciate any suggestions or solutions. Using Excel 2003. |
#2
|
|||
|
|||
Phil:
I also have a similar question to yours. I'd like to be able to sort among the data in each of the worksheets in a workbook and come up with totals. Is this possible???? "Phil" wrote: I have a workbook with six worksheets. Data (questions)in each worksheet are arranged in rows, and one column is a reference number for each question. I would like to know if it is possible to sort all the tabs together using that reference number. I assume the results would have to be produced on another tab, I can't think if there is another way to do it. I know I can sort each tab individually, I am looking to sort the data in entire workbook at once across all tabs. Appreciate any suggestions or solutions. Using Excel 2003. |
#3
|
|||
|
|||
Gina, I haven't received a response yet, and I have been searching the web
and cannot find it. I imagine it will be some type of macro, but it is outside my know how. If I get something I will let you know. "Gina" wrote: Phil: I also have a similar question to yours. I'd like to be able to sort among the data in each of the worksheets in a workbook and come up with totals. Is this possible???? "Phil" wrote: I have a workbook with six worksheets. Data (questions)in each worksheet are arranged in rows, and one column is a reference number for each question. I would like to know if it is possible to sort all the tabs together using that reference number. I assume the results would have to be produced on another tab, I can't think if there is another way to do it. I know I can sort each tab individually, I am looking to sort the data in entire workbook at once across all tabs. Appreciate any suggestions or solutions. Using Excel 2003. |
#4
|
|||
|
|||
Phil wrote...
Gina, I haven't received a response yet, and I have been searching the web and cannot find it. I imagine it will be some type of macro, but it is outside my know how. If I get something I will let you know. .... Consolidate all data onto a single worksheet, e.g., if you have data in A1:D1000 in Sheet1, Sheet2, Sheet3, Sheet4, Sheet5 and Sheet6, copy Sheet2!A1:D1000 and paste into Sheet1!A1001:D2000, copy Sheet3!A1:D1000 and paste into Sheet1!A2001:D3000, etc. Then sort the consolidated range in Sheet1, and finally copy each 1000-row portion from row 1001 into the other worksheets and clear Sheet1!A1001:D6000. There's no other practical way to do this. |
#5
|
|||
|
|||
Hi Harlan:
Thanks for the info. I was wondering if there was a way to sort among the tabs without having to make one very long spreadsheet. I am guessing by your answer that it is not possible to do this. I wanted to check before I combined all tabs into one sheet. --Gina "Harlan Grove" wrote: Phil wrote... Gina, I haven't received a response yet, and I have been searching the web and cannot find it. I imagine it will be some type of macro, but it is outside my know how. If I get something I will let you know. .... Consolidate all data onto a single worksheet, e.g., if you have data in A1:D1000 in Sheet1, Sheet2, Sheet3, Sheet4, Sheet5 and Sheet6, copy Sheet2!A1:D1000 and paste into Sheet1!A1001:D2000, copy Sheet3!A1:D1000 and paste into Sheet1!A2001:D3000, etc. Then sort the consolidated range in Sheet1, and finally copy each 1000-row portion from row 1001 into the other worksheets and clear Sheet1!A1001:D6000. There's no other practical way to do this. |
#6
|
|||
|
|||
Harlan,
Is there an easy way to do that, I have twenty tabs and the sheets have header sections throughout? A macro perhaps? "Harlan Grove" wrote: Phil wrote... Gina, I haven't received a response yet, and I have been searching the web and cannot find it. I imagine it will be some type of macro, but it is outside my know how. If I get something I will let you know. .... Consolidate all data onto a single worksheet, e.g., if you have data in A1:D1000 in Sheet1, Sheet2, Sheet3, Sheet4, Sheet5 and Sheet6, copy Sheet2!A1:D1000 and paste into Sheet1!A1001:D2000, copy Sheet3!A1:D1000 and paste into Sheet1!A2001:D3000, etc. Then sort the consolidated range in Sheet1, and finally copy each 1000-row portion from row 1001 into the other worksheets and clear Sheet1!A1001:D6000. There's no other practical way to do this. |
#7
|
|||
|
|||
Phil wrote...
Is there an easy way to do that, I have twenty tabs and the sheets have header sections throughout? A macro perhaps? .... Select the worksheets as a group, then select the common range to sort, then run the following macro. Sub smws() Dim wsc As Sheets, r As Range, xr As Range Dim i As Long, k As Long, n As Long, ra As String If Not TypeOf Selection Is Range Then MsgBox Prompt:="No range selected", Title:="smws - Halted" Exit Sub End If Set wsc = ActiveWindow.SelectedSheets Set r = Selection ra = r.Address If wsc.Count 1 Then k = r.Rows.Count n = k * wsc.Count If n Rows.Count - r.Row + 1 Then MsgBox Prompt:="Too many records to sort", Title:="smws - Halted" Exit Sub End If Set xr = r.Offset(k, 0).Resize(n - k, r.Columns.Count) If WorksheetFunction.CountA(xr) 0 Then MsgBox Prompt:="Insufficient free cells", Title:="smws - Halted" Exit Sub End If Application.ScreenUpdating = False Application.EnableEvents = False wsc(1).Select For i = 2 To wsc.Count wsc(i).Range(ra).Copy _ Destination:=r.Offset((i - 1) * k, 0).Cells(1, 1) Next i Union(r, xr).Select Application.EnableEvents = True Application.ScreenUpdating = True End If Application.Dialogs(xlDialogSort).Show If wsc.Count 1 Then Application.ScreenUpdating = False Application.EnableEvents = False For i = 2 To wsc.Count r.Offset((i - 1) * k, 0).Copy _ Destination:=wsc(i).Range(ra) Next i xr.Clear wsc.Select r.Select Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bringing data from one workbook to another using a date stamp func | Excel Worksheet Functions | |||
Formula contains same data when i Save As a workbook with another | Excel Discussion (Misc queries) | |||
Problem making versions of a table by changing sourse data in another workbook | Excel Discussion (Misc queries) | |||
link data to new workbook | Excel Discussion (Misc queries) | |||
Using Excel workbook as data source for word mailmerge | Excel Discussion (Misc queries) |