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

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(655 36,1).end(xlup).offset(1,0)


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=546033

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

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

Eddie


--
punter


------------------------------------------------------------------------
punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044
View this thread: http://www.excelforum.com/showthread...hreadid=546033