Sorting Rows Across Multiple Sheets

Question

I have a Worksheet with about 1000 items listed in order by a proprietary "sort code" that is held in column "A".  The varies rows in this sheet contain data relevant to the item (ie upc, manufacturer name, manufacture catalog number, supplier part no, etc, etc, etc.....)  Another sheet is filled of with numbers listing quantities of each item group together in a column, the quantity is listed in the row that corresponds to the row the item is listed in.

When more items are added to the database I want to run a routine on the worksheet on the deactivate event.  But if Items are added while running the program while quantites are already added on the second sheet the quantites will no longer correspond to the correct item.

Is there a way to sort the rows on the second sheet by column "A" in the first sheet. Or sort them at the same time??

I have designed this entire application and didn't realize this problem until I started testing it and needed an item not already in the database of items I added it and then was about to sort it and realized it would messes up my second sheet.

Answer 1

Here is a _Deactivate() routine that will sort the sheet just deactivated and two others that perhaps you can use as a model.  It's pretty fast - sorting 1000 rows with columns A:I sorted on 2 of them, and A:F sorted on the 3rd one.

And here is a web page showing how to add code to a worksheet's code module:

http://www.contextures.com/xlvba01.html#Worksheet

NOTE: these sort routines only work in Excel 2007/2010, they will fail in 2003 and earlier as sorting setup in them was different.  If you need code to use in 2003 also (which would also work in 2007/2010), let me know.

Private Sub Worksheet_Deactivate()

  Const FirstSortCol = "A"

  Const LastSortCol = "I"

  Const S3_LastSortCol = "F" ' see below

  Const SortKeyCol = "A"

  Const FirstDataRow = 2 ' assumes labels in row 1

  Dim anyWS As Worksheet

  Dim lastRow As Long

  'first we sort the sheet just deactivated

  Set anyWS = Me

  lastRow = anyWS.Range(SortKeyCol & Rows.Count).End(xlUp).Row

  anyWS.Sort.SortFields.Clear

  anyWS.Sort.SortFields.Add Key:=Range(SortKeyCol & 1), _

      SortOn:=xlSortOnValues, _

      Order:=xlAscending, _

      DataOption:=xlSortTextAsNumbers

  With anyWS.Sort

      .SetRange Range(FirstSortCol & FirstDataRow _

       & ":" & LastSortCol & lastRow)

      .Header = xlNo

      .MatchCase = False

      .Orientation = xlTopToBottom

      .SortMethod = xlPinYin

      .Apply

  End With

  '

  'now we go on to sort another sheet

  Set anyWS = Worksheets("Sheet2")

  'assumes it is set up just like the first one

  anyWS.Sort.SortFields.Clear

  anyWS.Sort.SortFields.Add Key:=Range(SortKeyCol & 1), _

      SortOn:=xlSortOnValues, _

      Order:=xlAscending, _

      DataOption:=xlSortTextAsNumbers

  With anyWS.Sort

      .SetRange Range(FirstSortCol & FirstDataRow _

       & ":" & LastSortCol & lastRow)

      .Header = xlNo

      .MatchCase = False

      .Orientation = xlTopToBottom

      .SortMethod = xlPinYin

      .Apply

  End With

  'now a 3rd sheet, but set up slightly

  'differently, only want to sort columns A:F, not A:I

  'so we use S3_LastSortCol in this instead of LastSortCol

  Set anyWS = Worksheets("Sheet3")

  anyWS.Sort.SortFields.Clear

  anyWS.Sort.SortFields.Add Key:=Range(SortKeyCol & 1), _

      SortOn:=xlSortOnValues, _

      Order:=xlAscending, _

      DataOption:=xlSortTextAsNumbers

  With anyWS.Sort

      .SetRange Range(FirstSortCol & FirstDataRow _

       & ":" & S3_LastSortCol & lastRow)

      .Header = xlNo

      .MatchCase = False

      .Orientation = xlTopToBottom

      .SortMethod = xlPinYin

      .Apply

  End With

  'some housekeeping cleanup

  Set anyWS = Nothing

End Sub

Answer 2

What about a simple COPY macro?  I set up a worksheet with all cells in the range of A1:AE100000 with an integer number in them.  3,200,000 cells in all.I can copy and paste those cells to two other sheets in VBA code in about 1/4-1/2 second (I didn't put a microtimer on it).

Sub CopyIt()

  Dim sourceWS As Worksheet

  Dim destWS As Worksheet

  Dim copyRange As Range

  Set sourceWS = Worksheets("Sheet1")

  Set destWS = Worksheets("Sheet2")

  Set copyRange = sourceWS.UsedRange

  copyRange.Copy destWS.Range("A1")

  Set destWS = Worksheets("Sheet3")

  copyRange.Copy destWS.Range("A1")

  Set copyRange = Nothing

  Set destWS = Nothing

  Set sourceWS = Nothing

End Sub

Windows 7, Excel 2010, CPU: Intel i7 Extreme at 3.33GHz and 12GB RAM.

Realizing you probably have other things being done on the other sheets, you'd want to 'tune' the routine so that it only copied the needed columns/rows of information so that any formulas and such referencing the values on the other sheets wouldn't get overwritten.

I don't think I know enough about what's going on in the 'big picture' of your workbook to make any real suggestion for a solution.  I'm sure that a routine could be built to run on the "Deactivate" event for the one sheet - or for the Activate event of others (although that might create a small delay when choosing them).

I'll try to come up with a 'generic' sorting routine that you can change some values in easily to adapt it to your workbook.  To be real close at the very start, I need to know the leftmost ("A") and rightmost ?? columns that must be included in the sort, and what row the data actually starts on in each sheet.  If all sheets have labels in the 1st row, then that would be row 2.  Also, a column (again, "A"??) that will always have an entry in it for all used data entry rows on a sheet.

Each sheet could be set up with different leftmost/rightmost/starting rows for the sorts on them.

Also, knowing which columns are the ones to use for the sort key(s) would be a good thing -- oh, and how many sort keys?  Just the one column A?

Answer 3

Dear JLLatham

I tried below macro and its very effective but it is limited to 3 sheets only.

Kindly write the same with max nos of sheet.

Sub CopyIt()

  Dim sourceWS As Worksheet

  Dim destWS As Worksheet

  Dim copyRange As Range

  Set sourceWS = Worksheets("Sheet1")

  Set destWS = Worksheets("Sheet2")

  Set copyRange = sourceWS.UsedRange

  copyRange.Copy destWS.Range("A1")

  Set destWS = Worksheets("Sheet3")

  copyRange.Copy destWS.Range("A1")

  Set copyRange = Nothing

  Set destWS = Nothing

  Set sourceWS = Nothing

End Sub

Answer 4

That would work, however I was looking for a solution that would keep my Template file as small as possible.  This application's end users will want it to run at top notch speed and I already have two other sheets doing the exact same thing that serves different capacities in the application.

FYI  This will be the third time I have completed this project from scratch, the other two time the files size became too large and sluggish so my objective with this latest version is speeding up all the procedures.

Answer 5

What about this: over on the second sheet (or start with a new one just to test this out), in Cell A1 put this formula:

=Sheet1!A1

where Sheet1 is the name of the sheet that you want to sort and potentially add entries to.  Fill that formula over to the right as far as you need to, then select all of the cells in that row and fill it down as far as you need to, plus maybe an extra hundred rows or more just so you don't have to mess with it again any time soon.  But you will have to check on it every now and then to make sure it is picking everything up.

This way when you sort the source data or add to it, it will automatically show up in the proper location on this copy sheet.