Not sure if explaning this right but I'll try

[punter]

Hi,

I have a sheet with data for about 200 stores on it. The data is listed as such:

Store Product Amount
12 abc $1.00

As I said I have many different store numbers on the same sheet. I want to put each stores into its own workbook. I don't want to be cutting and pasting for a really long time if I can avoid it. Here is my question: What do I have to do to have excel put all the data for each store (there are most likely multiple lines for each store) into its own workbook, save the file into a folder with the store # as the name, and repeat for all the rest.

I could probaly get the macro to work if I knew how to get it to read each store number and paste it onto its own sheet.

Any help would be most welcome. This is something I'm going to have to be doing every week and don't look forward to cutting and pasting my life away.

Thanks

Eddie.

[mrice]

I would suggest that you sort the original so that the store lines appear in blocks. Then use you macro to check on the contents of the cells. If a new store is encountered use

workbook.open filename:= StoreA.xls

If this workbook doesn't already exist use

Workbooks.add
Activeworkbook.saveas filename:= StoreA.xls

Then copy the data across

Rows(N).copy destination: = Workbooks("StoreA.xls").sheets("Sheet1").cells(65536,1).end(xlup).offset(1,0)

[Kevin B]

If tested this and it worked Okay, however, I did not have a header row for
the stores, the data started in cell A1 of Sheet1.

First I sorted the data by store. I then compared 2 variable values varVal1
and varVal2 and noted when varVal2 did not = varVal1, indicating a change in
stores.

I then selected the rows for the current store, copied them, inserted a new
worksheet and pasted the rows into the new sheet, naming the sheet after the
store.

It then went back to the source worksheet, deleted the copied rows and
restarted the loop.

Maybe this or a variation might work.

Sub ParseStores()

Dim wb As Workbook
Dim ws As Worksheet
Dim varVal1 As Variant
Dim varVal2 As Variant
Dim intOffset As Integer
Dim blnFound As Boolean

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

ws.Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Name = "Stores"
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
varVal1 = ActiveCell.Value
With Application
.ScreenUpdating = False
.StatusBar = "Evaluating store " & varVal1
End With

Do Until varVal1 = ""
Do Until blnFound
varVal2 = ActiveCell.Offset(intOffset + 1).Value
If varVal2 <> varVal1 Then
Rows("1:" & intOffset + 1).Select
Selection.Copy
wb.Worksheets.Add
With ActiveSheet
.Paste
.Name = varVal1
End With
ws.Select
Rows("1:" & intOffset + 1).Select
Selection.EntireRow.Delete
intOffset = 0
blnFound = True
End If
intOffset = intOffset + 1
Loop
blnFound = False
Range("A1").Select
varVal1 = ActiveCell.Value
Application.StatusBar = "Evaluating store " & varVal1
Loop

Set wb = Nothing
Set ws = Nothing
Exit Sub

End Sub

--
Kevin Backmann


"punter" wrote:

>
> Hi,
>
> I have a sheet with data for about 200 stores on it. The data is
> listed as such:
>
> Store Product Amount
> 12 abc $1.00
>
> As I said I have many different store numbers on the same sheet. I
> want to put each stores into its own workbook. I don't want to be
> cutting and pasting for a really long time if I can avoid it. Here is
> my question: What do I have to do to have excel put all the data for
> each store (there are most likely multiple lines for each store) into
> its own workbook, save the file into a folder with the store # as the
> name, and repeat for all the rest.
>
> I could probaly get the macro to work if I knew how to get it to read
> each store number and paste it onto its own sheet.
>
> Any help would be most welcome. This is something I'm going to have to
> be doing every week and don't look forward to cutting and pasting my
> life away.
>
> Thanks
>
> Eddie.
>
>
> --
> punter
>
>
> ------------------------------------------------------------------------
> punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044
> View this thread: http://www.excelforum.com/showthread...hreadid=546033
>
>

[punter]

Thank you very much for the replies. I will kick it around now and see if I can make it work.

Eddie