First, I wouldn't make this kind of thing automatic. I'd make a macro and run
it on demand. Then I could make as many errors in typing that I want and not
have them propogated to other workbooks/worksheets.
I have to do the same kind of thing. This may not work for you, but...
I dedicate one column (A) for my my Customer Indicator.
I put abbreviations in each cell in each row.
X
CA,IN,IL
CA,IL
NY,MA
X
Z
(I used state abbreviations just for examples, but X means always keep it--Z
means it's my record only--not to be disseminated--no blanks allowed!)
Then I do the same thing in Row 1.
Then since I know the list of abreviations, I can do stuff to it.
Say I had 5 customers.
I copy the worksheet, then clean up the rows and columns that don't belong by
looping through column A and row 1.
Then I repeat until I'm out of customers.
If you can set the file|pagesetup nicely, you won't have much work to do after
it's split up.
In fact, you could add some individuality to each sheet.
If you like this idea, maybe this will get you started:
Option Explicit
Sub testme()
Application.ScreenUpdating = False
Dim CustList As Variant
Dim mstrWks As Worksheet
Dim wks As Worksheet
Dim wksName As String
Dim KeyString As String
Dim iRow As Long
Dim iCol As Long
Dim iCtr As Long
Set mstrWks = Worksheets("masterPrice")
CustList = Array("CA", "IN", "IL", "NY", "MA")
For iCtr = LBound(CustList) To UBound(CustList)
mstrWks.Copy _
after:=mstrWks
Set wks = ActiveSheet
With wks
wksName = "Customer--" & CustList(iCtr)
Application.DisplayAlerts = False
On Error Resume Next
Worksheets(wksName).Delete
On Error GoTo 0
Application.DisplayAlerts = False
.Name = wksName
'clean up columns
For iCol _
= .Cells(1, .Columns.Count).End(xlToLeft).Column To 2 Step
-1
KeyString = "," & _
Application.Substitute(.Cells(1, iCol).Value, " ", "") &
","
If InStr(1, KeyString, "," & CustList(iCtr) & ",", _
vbTextCompare) > 0 _
Or InStr(1, KeyString, ",x,", vbTextCompare) > 0 Then
'keep this one
Else
.Columns(iCol).Delete
End If
Next iCol
For iRow = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1
KeyString = "," & _
Application.Substitute(.Cells(iRow, 1).Value, " ", "") & ","
If InStr(1, KeyString, "," & CustList(iCtr) & ",", _
vbTextCompare) > 0 _
Or InStr(1, KeyString, ",x,", vbTextCompare) > 0 Then
'keep this one
Else
.Rows(iRow).Delete
End If
Next iRow
.Rows(1).Delete
.Columns(1).Delete
With .Range("a1")
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
.Range("B1").Value = "For Customer: " & CustList(iCtr)
End With
Next iCtr
Application.ScreenUpdating = True
MsgBox "done!"
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
rhea < said:
ok so i'm extremely new to programming in excel, mostly because i've
never had to so i haven't quite got the hang of it and i'd really
appreciate any help y'all could give me.
so to be more descriptive than my subject line gives:
what i have is a master price list for a bunch of properties. but they
don't all need the same stuff so for each item number i have the
locations that use it designated. what my boss wants me to do is create
separate sheets for each location that will take only the items that it
uses. so how can i create a function that will do this for me? [also we
want it to continue working in the future so when anyone updates the
list it will update the other sublists as well. basically so we only
ever have to update the master but can still give updated copies to
each separate property.]
