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.

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.

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.

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.

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.

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.

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

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