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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,455
Messages
5,572,235
Members
412,449
Latest member
mdvouf
Top