sorting and copying
 

dont know if this it to much to do via a script but here goes
i have a file with 700 lines or so by 14 columns of data , in the 4th column
i have a list of numbers ,
what i am after is someway of when a change in the number in the 4th column
appears,select all the data above with the same number and either copy/cut
that data to a new file or even a new tab (naming that tab to the same as the
number) in the same file , then continue till the next change in column 4
and so on till the page is empty. so i end up with a file full of tabs for
each number change....

maybe its to much , thanks for trying in advance

sorting and copying
 

Try sorting all 14 columns based on column 4 (ascending or descending it
doesn't matter) then place a filter on column 4 your code should be a for
next loop. Inside the loop will go the filtering based on x and the selection
of the visible area plus cutting or copying to a new page. Maybe you should
post in the excel.coding and not on this board. Just a suggestion.
i.e.:

for i=x to y

next x

Michael Arch.

"Stan Halls" wrote:

dont know if this it to much to do via a script but here goes
i have a file with 700 lines or so by 14 columns of data , in the 4th column
i have a list of numbers ,
what i am after is someway of when a change in the number in the 4th column
appears,select all the data above with the same number and either copy/cut
that data to a new file or even a new tab (naming that tab to the same as the
number) in the same file , then continue till the next change in column 4
and so on till the page is empty. so i end up with a file full of tabs for
each number change....

maybe its to much , thanks for trying in advance

sorting and copying
 

Stan,

Select a cell withing your database, and then un the macro below.

Answer 4 when asked..."What column # within database to use as key?"

HTH,
Bernie
MS Excel MVP


Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")


Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName =Worksheets(CStr(myCell.Value)).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = CStr(myCell.Value)
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
'These lines copy everything - including extra header rows
' and any SUBTOTAL formulas separated by blank row
'Uncomment them to use them
' myCell.Parent.Cells.SpecialCells(xlCellTypeVisible ).Copy
' mySht.Range("A1").PasteSpecial xlPasteValues


'These are the default - only copy the database values
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub



"Stan Halls" wrote in message
...
dont know if this it to much to do via a script but here goes
i have a file with 700 lines or so by 14 columns of data , in the 4th column
i have a list of numbers ,
what i am after is someway of when a change in the number in the 4th column
appears,select all the data above with the same number and either copy/cut
that data to a new file or even a new tab (naming that tab to the same as the
number) in the same file , then continue till the next change in column 4
and so on till the page is empty. so i end up with a file full of tabs for
each number change....

maybe its to much , thanks for trying in advance

sorting and copying
 

Brenie
Magic, thats just what i wanted , thanks for taking the time to do
that

"Bernie Deitrick" wrote:

Stan,

Select a cell withing your database, and then un the macro below.

Answer 4 when asked..."What column # within database to use as key?"

HTH,
Bernie
MS Excel MVP


Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")


Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName =Worksheets(CStr(myCell.Value)).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Befo=Worksheets(1))
mySht.Name = CStr(myCell.Value)
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
'These lines copy everything - including extra header rows
' and any SUBTOTAL formulas separated by blank row
'Uncomment them to use them
' myCell.Parent.Cells.SpecialCells(xlCellTypeVisible ).Copy
' mySht.Range("A1").PasteSpecial xlPasteValues


'These are the default - only copy the database values
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub



"Stan Halls" wrote in message
...
dont know if this it to much to do via a script but here goes
i have a file with 700 lines or so by 14 columns of data , in the 4th column
i have a list of numbers ,
what i am after is someway of when a change in the number in the 4th column
appears,select all the data above with the same number and either copy/cut
that data to a new file or even a new tab (naming that tab to the same as the
number) in the same file , then continue till the next change in column 4
and so on till the page is empty. so i end up with a file full of tabs for
each number change....

maybe its to much , thanks for trying in advance