linked sheets sort all sheets

jordash

New Member
Joined
Sep 20, 2005
Messages
7
I have a master sheet with approx 30 columns. I have a sheet linked to the master sheet using the first 10 columns (called sheet 2). In sheet 2 I have unique information in the next 10 columns, now 20 columns.
What happens is when I sort the master sheet the linked columns in sheet 2 sort the same as the master but the unique columns do not sort. This means each row of data is now corrupt.
Am I doing something wrong or is this how excel works?
Would appreciate any help anyone can give please.
Thanks
Jordash
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
both :)

That IS how Excel works and the only thing you may have done wrong is a design issue.
 

jordash

New Member
Joined
Sep 20, 2005
Messages
7
Ok thank you for that.
Do you have any idea how I could re-desing this to allow me to still use two seperate sheets with linked information
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, jordash!

to my sense it would be a good idea to post some sample data and formulas how your sheets are "linked", how you want to sort ...
keep it simple

kind regards,
Erik
 

jordash

New Member
Joined
Sep 20, 2005
Messages
7

ADVERTISEMENT

Here is a partial sample of what I have. In the first 3 columns the data is the same - sheet 2 is linked to data in master. The next columns contain different data in each sheet, so that if I sort master it also sorts the 3 columns in sheet 2 but not the next columns so therefore the data in the rows becomes corrupt

Master sheet
Claim Surname Property District Code
10/00001 Rob Shamrock GT T
10/00002 Eba 29 Martin GT A1
10/00003 Adol 29 Martin GT X

sheet2
Claim Surname Property Furniture Be
10/00001 Rob Shamrock Beacon Y
10/00002 Eba 29 Martin Beacon N
10/00003 Ado 29 Martin Beacon Y

sheet2 links
=Master!A1 =Master!B1 =Master!C1
=Master!A2 =Master!B2 =Master!C2
=Master!A3 =Master!B3 =Master!C3
=Master!A4 =Master!B4 =Master!C4

Thanks for your help so far
Jordash
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
updated some code I made a year ago ...
try this
Code:
Option Explicit
Option Base 1

Sub SortEntireCells()
'Erik Van Geit
'051211 2206

'"SORT" selected range
'cutting and inserting rows, so that links to the actual
'cells will "follow" the cells after they are sorted.
'
'The selected data range should contain headers/field names.
'The sort "key" will be the header in the column of the active cell.
'The range to be sorted can be one or several columns.
'
'EXAMPLE
'BEFORE
'sheet1     sheet2      display
'H1         =sheet1!A1  H1
'a3         =sheet1!A2  a3
'a1         =sheet1!A3  a1
'a2         =sheet1!A4  a2
'AFTER
'sheet1     sheet2      display (not changed)
'H1         =sheet1!A1  H1
'a1         =sheet1!A4  a3
'a2         =sheet1!A2  a1
'a3         =sheet1!A3  a2

    Dim rngSort As Range
    Dim i As Long
    Dim rr As Long
    Dim ori As Long
    Dim rCount As Long
    Dim cCount As Integer
    Dim keyOffset As Integer
    Dim rc As Integer
    Dim rcc As Integer
    Dim OriginalRows As Variant
    Dim wbSort As Workbook
    
    Set wbSort = ActiveWorkbook
    Set rngSort = Selection
    keyOffset = ActiveCell.Column - Selection.Column
    
    rCount = rngSort.Rows.Count
    cCount = rngSort.Columns.Count
    
    ReDim OriginalRows(rCount) As Variant
    
    With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
        
        Selection.Copy
        Workbooks.Add
        ActiveSheet.Paste
        Cells(1, cCount + 1).Value = "Original Row"
    
        Dim rng As Range
        Set rng = Range(Cells(2, cCount + 1), Cells(rCount, cCount + 1))
        With rng
        .Formula = "=Row()"
        .Value = .Value
        End With
        
        ActiveSheet.UsedRange.Sort Key1:=Cells(1, 1).Offset(0, keyOffset), order1:=xlAscending, Header:=xlYes
        
        OriginalRows = rng 'using "computed array"
        
        .DisplayAlerts = False
        ActiveWorkbook.Close
        .DisplayAlerts = True
     
        wbSort.Activate
        
        rr = rngSort.Row
        rc = rngSort.Column + cCount
        rcc = rngSort.Column + cCount * 2 - 1
        .DisplayStatusBar = True
        
Cells(1, rngSort(1).Column).Resize(Rows.Count, cCount).Insert

        For i = LBound(OriginalRows, 1) To UBound(OriginalRows, 1)
            ori = OriginalRows(i, 1)
            Range(Cells(ori, rc), Cells(ori, rcc)).Cut Range(Cells(i + rr, 1), Cells(i + rr, cCount))
            .StatusBar = "sorting: " & Round(i / rCount * 100, 0) & "%"
        Next
        Range(Cells(rr + 1, 1), Cells(rr + rCount - 1, cCount)).Cut Range(Cells(rr + 1, rc), Cells(rr + rCount - 1, rc + cCount - 1))
        
Cells(1, 1).Resize(Rows.Count, cCount).Delete

    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .StatusBar = False
    End With
End Sub
 

ShannonW

New Member
Joined
Apr 29, 2005
Messages
8

ADVERTISEMENT

Sort Code by erik.van.geit 11 Dec 2005

This code is wonderful for a project I am working on.
Is there a way to include range selection for the sheet being sorted?
I have a header row, and add data to rows daily.
Last col used is X.
Example
1 A B C ........... X
2 Last First Number Grade
3 Doe Jane 222 Z
4 Smith Ray 567 N
5
6
I tried adding lines such as: Range("a1:x" & [x65536].End(xlUp).Row).Select
But my attempts end up deleting cols or selecting too many rows. I have much to learn!
Thank you,
Shannon
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, ShannonW
Welcome to the Board !!!!!

Do you mean you want to avoid the manual selection.
to my sense you are close
did you try to replace
Code:
Set rngSort = Selection
by
Code:
Set rngSort = Range("A1:X" & Cells(Rows.Count, "X").End(xlUp).Row)

kind regards,
Erik
 

ShannonW

New Member
Joined
Apr 29, 2005
Messages
8
Thanks for the welcome. I've gained so much from reading this forum.
Yes, you are right - I want to avoid the manual selection.
I changed the Set rngSort, but nothing happened. Although (I hope this makes sense) I can tell that it's trying to do something, because all the rows "blink" for a fraction of a second from the first row to the last. Other than that, no sort; but no error!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
please post some sample data to work on
5 columns 10 rows would be enough
to get nice display you can use my add-in (see my signature) or colo's htmlmaker (see bottom of the page)

do you confirm you are using the code from above with one line changed as mentioned in my previous post ?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,003
Messages
5,834,828
Members
430,324
Latest member
bosphoruskid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top