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 |
#8
|
|||
|
|||
Harlan,
This is a good script, thank you. I am having a little trouble with it sorting all of my columns and objects so I was rethinking it might be easier to have a macro copy every row/column containing data and objects in each of my sheets and copy it to a new sheet. Then I can just use the Sort function on the particular column (containing the reference number) on that sheet containing all the data. Would you know of a macro that would do that? It would have to determine the number of rows in each of the worksheets, as they are all different (and are added and subtracted to regularly). They all have the same number and order of columns though. Thanks. "Harlan Grove" wrote: 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) |