Cycle Through Sheets & Sorting Named Ranges

Hi guys,

I need suggestions / assistance on sorting named ranges into a given order.

I have a raw data sheet that contains data for a given week - monday to sunday
The problem with this raw data is that it is not in the correct order. Data has been mistakenly exported out of order.
a quick example of this:
Vegetables-
..........Corn
..........Pear
..........Orange
Fruit-
..........Apple
..........Carrot
..........Lettuce
The data is out of order - the fruit is not listed correctly under the fruit similarly with vegetables

The goal of my macro is to do the following
Vegetables-
..........Corn
..........Carrot
..........Lettuce
Fruit-
..........Apple
..........Pear
..........Orange


A macro I already have goes through this raw data sheet and names the ranges appropriately by looking for top and bottom borders then saving those rows between Cols A to Col H as a named Range. The name of found range = the top left cell value within that named range

I also have 'reference' style worksheets. these sheets contain a list of data on them, appropriate to this it contains a list of names which correlate to the named ranges found on the raw data sheet above
this reference sheet should be used by the macro as a guide to which the macro copys that named range from the raw data sheet starting at the first name found on this sheet

Please see the attached workbook which clarifies what i've tried to explain above. Im open to suggestions on a different method for doing this - perhaps without using named ranges tho im not aware of any other methods that would achieve this.

Thanks heaps

Hi


See how this goes.

Sub aaa()
  Dim DataSH As Worksheet
  Set DataSH = Sheets("01-01-2012")
  arr = Array("Sarah", "Lauren", "Kym", "Tiffany", "John", "Stephen", "Peter", "Joshua")
  Sheets.Add after:=Sheets(Sheets.Count)
  ActiveSheet.Name = "temp"
  DataSH.Range("A1:H2").Copy Destination:=Range("A1")
  For i = LBound(arr) To UBound(arr)
    Range(arr(i)).Copy Destination:=Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1)
  Next i
    
  
  
End Sub

Note that your example file doesn't have a named range for Peter.

rylo

Thanks Rylo works well..
I do have another question tho how would i modify

arr = Array("Sarah", "Lauren", "Kym", "Tiffany", "John", "Stephen", "Peter", "Joshua")

Such that it is generic, instead of having the names manually written in the array have a reference to sheet 'Sarah' for all the girls names (note that the amount of names in the list is not a fixed amount) and also for John & the boys names (also not a fixed number of names in the list)

Regards

Hi

Rather than having 2 sheets, create a single sheet with the 2 lists in different columns. You could either loop through the items in the columns, or make them named ranges that could be actioned.

So if the female names are in the range A1:A... and the male names are in the range B1:B.... then something like

for i = 1 to cells(rows.count,1).end(xlup).row

range(cells(i,1)).....
next i

for i = 1 to cells(rows.count,2).end(xlup).row
  range(cells(i,2)).....
next i

naturally with something to nominate the relevant sheet that they are on...

HTH

rylo