rachelka
10-01-2008, 10:58 AM
Hi :help
I have problem with macro, because I don't know how to make it.
I have some sheet with data . In column B I have only names with yellow interior.
After each name I have a few rows with data concerning this person (data from column A till J).
There is no blank rows because before this macro I deleted them.
And what I want to do is to split all data from this one sheet to new workbooks. Each of this workbooks should have name of this person from B column and data after but without data for another person.
Each of this workbook I want to save in the same directory but with different name.
I was thinking about do loop, do while etc., but I don't know how to make it.
Below I made some code but I don't know what to do next.
This macro select for me the first selection to copy to new workbook.
But I have more this.
Sub CopyPastetonewWorkbook ()
Dim a As Range
Dim b As Range
Workbooks("x").Activate
Columns("B:B").Select
Application.FindFormat.Interior.ColorIndex = 6
Selection.Find(What:="*", After:=ActiveCell, LookIn:=xlValues, Lookat:= _
xlPart,SearchOrder:=xlByColumns,SearchDirection:=xlNext,MatchCase:= _False, SearchFormat:=True).Activate
Set a = ActiveCell
Selection.FindNext(After:=ActiveCell).Activate
Set b = ActiveCell
a.Offset(0, -1).Resize((b.Row - a.Row), 10).Copy
b=a
End Sub
I was thinking about something like that.
Selection.FindNext(After:=ActiveCell).Activate
Set b = ActiveCell
a.Offset(0, -1).Resize((b.Row - a.Row), 10).Copy
b=a
Selection.FindNext(After:=ActiveCell).Activate
Set b = ActiveCell
a.Offset(0, -1).Resize((b.Row - a.Row), 10).Copy
b=a
etc.
Any suggestions will be welcomed:think:
I have problem with macro, because I don't know how to make it.
I have some sheet with data . In column B I have only names with yellow interior.
After each name I have a few rows with data concerning this person (data from column A till J).
There is no blank rows because before this macro I deleted them.
And what I want to do is to split all data from this one sheet to new workbooks. Each of this workbooks should have name of this person from B column and data after but without data for another person.
Each of this workbook I want to save in the same directory but with different name.
I was thinking about do loop, do while etc., but I don't know how to make it.
Below I made some code but I don't know what to do next.
This macro select for me the first selection to copy to new workbook.
But I have more this.
Sub CopyPastetonewWorkbook ()
Dim a As Range
Dim b As Range
Workbooks("x").Activate
Columns("B:B").Select
Application.FindFormat.Interior.ColorIndex = 6
Selection.Find(What:="*", After:=ActiveCell, LookIn:=xlValues, Lookat:= _
xlPart,SearchOrder:=xlByColumns,SearchDirection:=xlNext,MatchCase:= _False, SearchFormat:=True).Activate
Set a = ActiveCell
Selection.FindNext(After:=ActiveCell).Activate
Set b = ActiveCell
a.Offset(0, -1).Resize((b.Row - a.Row), 10).Copy
b=a
End Sub
I was thinking about something like that.
Selection.FindNext(After:=ActiveCell).Activate
Set b = ActiveCell
a.Offset(0, -1).Resize((b.Row - a.Row), 10).Copy
b=a
Selection.FindNext(After:=ActiveCell).Activate
Set b = ActiveCell
a.Offset(0, -1).Resize((b.Row - a.Row), 10).Copy
b=a
etc.
Any suggestions will be welcomed:think: