Array usage
 

Hi. I think I need to use an array to accomplish what I'm trying to d
in an Excel file, but am not sure.

Currently, I have an Excel workbook with several store numbers and whe
I run a macro the sheet is sorted and each unique store number is copie
and saved into a new workbook. This code is working fine, but I need t
add some further functionality to it.
Certain store numbers have sister stores that need to be copied int
the parent workbook. For example, store 123 has no sister stores an
is copied and saved into 123.xls. However, store 456 has sister stor
789 and file 456.xls is created with only store 456 data, but not 78
data. I need some code to say if current store is 456, then copy 45
AND 789 into 456.xls. There are about 500 stores of which only about
have sister stores so I think creating a static array wouldn't be to
bad.

Any thoughts? Any help would be much appreciated.
Thank

--
brocklander
-----------------------------------------------------------------------
brocklanders's Profile: http://www.excelforum.com/member.php...fo&userid=3520
View this thread: http://www.excelforum.com/showthread.php?threadid=54969

Array usage
 

Dear Brocklander,

I read the description of your problem 4 times over, but still can't
figure out what you are trying to convey. Can you define input,
processing and desired output in a structural way preferably
accompanied by an example. This will make chances bigger that you get a
response.
Maybe put a relevant part of your code in as well.

Good luck

Array usage
 

lexcel

Some further explanation.

I have 1 worksheet and only columns A and B contain data. The data in
column A is store numbers and column B is territory number. Store
number is a unique value so there are no duplicates. Territory number
does have duplicates. Currently. when I run a macro I created, the
sheet is sorted by Territory number. Then, all store numbers
corresponding to that terrritory number are copied and saved into a new
workbook and the workbook is saved as the territory number.xls. See
below for example.
Store Territory
1 101
2 101
3 101
4 101
5 102
6 103
7 105
8 105
Notice stores 1-4 have the same territory number. With my macro,
stores 1-4 would be saved into a file called 101.xls. Only store 5
would be saved into 102.xls and so on. This code is working fine, but
I need to add a new twist.
Now, store 1 has a sister store in store 6. So, I need to add store 6
to 101.xls even though it's a different territory number. There's no
real logic about which stores have sister stores, so I think it's
something I'll have to hard code, which is fine because not many stores
have sister stores.
I apologize for giving the abridged version of my problem earlier. It
probably just led to more confusion.
Thanks again


--
brocklanders
------------------------------------------------------------------------
brocklanders's Profile: http://www.excelforum.com/member.php...o&userid=35205
View this thread: http://www.excelforum.com/showthread...hreadid=549697

Array usage
 

Hi Mr Brocklander

The problem was indeed not as trivial as I thought at first. And I hope
I understood this time what you want. If so, the code hereafter should
do the job.

If a sister store exists it is expected in column C, e.g.
A B C
1 101 6
2 101
3 101 8
4 101
5 102
6 103
7 105
8 105

The records 6 103 and 8 105 will be added tot file 101.xls


Option Explicit
Const StoreCol = 1, TerCol = 2, SisCol = 3
Private CurrentSheet As Worksheet, NextRow As Long
Sub BL()
Dim Ro As Long, Cro As Long, Lastrow As Long
Dim LookInCol As Long, Store As Long, SisterStore As Long
Dim Terry As String, LookFor As String
Dim ra As Range

Application.ScreenUpdating = False
ActiveSheet.Cells.Copy
Worksheets.Add
Cells.PasteSpecial xlPasteAll
Application.CutCopyMode = False

Do
Ro = 1
Terry = Cells(Ro, TerCol)
If Len(Terry) = 0 Then Exit Do
Call NewFile(Terry)
Do
Call WriteRow(Ro)
SisterStore = Cells(Ro, SisCol)
Rows(Ro).Delete
LookInCol = TerCol
LookFor = Terry
If SisterStore Then
If WorksheetFunction.CountIf(Columns(StoreCol),
SisterStore) Then
LookFor = SisterStore
LookInCol = StoreCol
End If
End If
Set ra = Columns(LookInCol).Find(LookFor, LookIn:=xlValues,
lookat:=xlWhole)
If ra Is Nothing Then Exit Do
Ro = ra.Row
Loop
Call CloseFile
Loop

With Application
.DisplayAlerts = False
ActiveSheet.Delete
.DisplayAlerts = True
.ScreenUpdating = True
End With

End Sub

Sub NewFile(FileName As String)
Dim s As Worksheet

Set s = ActiveSheet
Set CurrentSheet = Worksheets.Add
CurrentSheet.name = FileName
NextRow = 1
s.Activate

End Sub

Sub WriteRow(r As Long)
Dim s As Worksheet

CurrentSheet.Rows(NextRow).Value = Rows(r).Value
NextRow = NextRow + 1

End Sub

Sub CloseFile()
Dim FilNam As String

With CurrentSheet
FilNam = .name
.Columns(SisCol).Delete xlShiftToRight ' remove the Sister
Store column from the result
.Move ' to a new workbook
End With

ActiveWorkbook.SaveAs FilNam
ActiveWorkbook.Close

End Sub