Ok, here is how i changed the data, I had to restructure the headings to get the advanced filter to work. The code to create the sheets is here:
It will create the sheets but is not putting any data in the sheets as it sits now. Once all of this data goes into the other sheets there is a very large amount of calculations that come next, so i almost think it would be better to leave the sheets permanent, and have the code just rename them based on the unique list advanced filter creates on the "strata" sheet. That way the calculations and graphs will not have to be reconstructed or paste in.
'Create Sheets Named After Rock Rock Types
Public Sub Strata()
Dim rRange As Range, rCell As Range
Dim wSheet As Worksheet
Dim wSheetStart As Worksheet
Dim strText As String
Set wSheetStart = ActiveSheet
wSheetStart.AutoFilterMode = False
'Set a range variable to the correct item column
Set rRange = Range("A6", Range("A65536").End(xlUp))
'Delete any sheet called "Strata"
'Turn off run time errors & delete alert
On Error Resume Next
Application.DisplayAlerts = False
'Add a sheet called "Strata"
Worksheets.Add().Name = "Strata"
'Filter the Set range so only a unique list is created
rRange.AdvancedFilter xlFilterCopy, , _
'Set a range variable to the unique list, less the heading.
Set rRange = .Range("A2", .Range("A65536").End(xlUp))
On Error Resume Next
For Each rCell In rRange
strText = rCell
.Range("A6").AutoFilter 1, strText
'Add a sheet named as content of rCell
Worksheets.Add().Name = strText
.AutoFilterMode = False
On Error GoTo 0
Application.DisplayAlerts = True
also, for some reason i can not get .zip files to compress smaller than .rar's this file is 42kb as a .rar and was 56kb as a .zip
Auto Merged Post Until 24 Hrs Passes;
Ger, do you think it would be possible to encorporate your code into the code above, since hte code above could copy each grouping into the seperate sheets, could your code copy the data the correct number of times overitself in the sheet?
For instance, once all the basalt values were put into the basalt sheet, then your code copies each row the number of times specified, but places it over the data it copied instead of into a new sheet. If this is possible I think it would be the best bet