link sheets 1 and 2 and autosort sheet 2

[RonB]

Hello I have a spreadsheet with 20 columns. The columns alternate between text and number data. Row 1 is a header row and is not sorted. The data is automatically sorted in descending order on the number columns which also sorts the text with the numbers. The sorting is done during data entry. Each number column sorts seperately from the other, please note attachment below. I am using excel 2002. The below code runs sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim b As Long
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
For b = 2 To 20 Step 2
If Not Intersect(Columns(b), Target) Is Nothing Then
Range(Columns(b - 1), Columns(b)).Sort Key1:=Cells(1, b), Order1:=xlDescending, Header:=xlYes
End If
Next b
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I would like to link sheet1 to sheet 2 in order to populate both sheets at the same time. On sheet 2 I would like to automatically sort on the text columns in ascending order as opposed to the number columns in sheet1. Is there additional code I could use either in sheet1 or sheet2 that will accomplish that task? I am using excel 2002

 

[Brianwarnock]

To populate multiple sheets with the same data whilst only entering it in 1 you group the sheets, if its not all sheets select the sheets while holding the Ctrl key, however I'm not sure how much sense this makes if you are autosorting the 2 sheets differently, correcting a typo would be a problem.

If you go ahead with this then you would need to run a modified version of your code for sheet2.

Brian

 

[RonB]

Hi Brian, thanks for your response. Others have also told me that the combination of autosort and linking two sheets could be a problem. I modified my code for sheet 2, which gave me the autosort, but the data did not sort properly because of the conflict between the sort orientations. I solved the problem by not linking the two sheets. I simply select and copy all from sheet1 and paste in sheet2. It is not exactly what I wanted to do, but it works, and eliminates the tedium of retyping data.

Thanks
Ron

 

[Brianwarnock]

Sometimes we cannot have exactly what we want, I'm glad that you have a workable solution, thanks for coming back.

Brian