Volker,
What my code does is to look at a worksheet called "Job Cost Summary".
Each row on this worksheet has information about a projects and column F
contains the name of the manager of that project. I need to send each
project manager information about their own projects. So...
First I build a collection from the contents of column F that contains
each manager's name once. Then I loop through the collection, and put
the value of each item in the collection into a filter criteria range.
Then I do the filter and copy the results into a new workbook. Once
that's created I get the next item in the collection and do a filter on
that value, then copy the results to a new workbook, and so on.
Where are you running into problems? What is the structure of your data?
What are you trying to do? If you post the code you're using and let me
know where it's falling over, I'll try to help.
--
HTH,
Dianne
In ,
Volker Hormuth typed:
Hi Dianne,
please show me the structure of your datatable or send a workbook to
me. I got a mistake.
Thanks !
Volker
"Dianne" schrieb im Newsbeitrag
...
Here's a really chopped up version of a macro I use to send data to
each of our managers. Be warned that you'll have to do some
modification. You could also modify the section where I save to
email it instead.
Sub CreatePMWorkbooks()
Dim wkbk As Workbook
Dim cell As Range
Dim colManagers As New Collection
Dim wsData As Worksheet
Dim ws As Worksheet
Dim vntManager As Variant
Dim lngNumRows As Long
Dim strName As String
Set wsData = ActiveWorkbook.Worksheets("Job Cost Summary")
Application.StatusBar = "Creating Workbooks. Please wait..."
Application.ScreenUpdating = False
'Count the number of rows
lngNumRows = wsData.Range("F65536").End(xlUp).Row
'Create a collection of managers
On Error Resume Next
For Each cell In wsData.Range("E6:E" & lngNumRows)
If cell.Value = "" Then cell.Value = "Unknown"
colManagers.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0
'Filter on each manager and print
For Each vntManager In colManagers
Set wkbk = Application.Workbooks.Add
'Plug in each manager's name into the filter criteria range
wsData.Range("F2").Value = vntManager
'Add a new worksheet
wkbk.Sheets.Add befo=wkbk.Worksheets("Sheet1")
Set ws = ActiveSheet
ws.Name = vntManager
'Copy the field names from the original worksheet
wsData.Range("1:3").Copy ws.Range("1:3")
'Filter the data and copy to the new workbook
wsData.Range("A5").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, criteriarange:=wsData.Range("F1:F2"), _
copytorange:=ws.Range("A5")
'By default my new workbooks are created with 3 sheets
'This step deletes these sheets
If wkbk.Sheets.Count 3 Then
Application.DisplayAlerts = False
wkbk.Worksheets("Sheet1").Delete
wkbk.Worksheets("Sheet2").Delete
wkbk.Worksheets("Sheet3").Delete
Application.DisplayAlerts = True
End If
'Create a name for the workbook and save it
strName = "C:\Docs\Job Cost Summary " & vntManager
wkbk.SaveAs (strName)
wkbk.Close (False)
Next vntManager
'Clear my Filter Range
wsData.Range("F1:F2").Clear
LeaveSub:
Set colManagers = Nothing
Set cell = Nothing
Set wsData = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
In ,
hef typed:
I have one file with information on 833 suppliers.
I have to send to each supplier a list of only their parts, have
them fill in one column and then send me their file back. I could
send them a hard copy...but then it would take me forever to
compile the data when the information's returned.
We figured it would be easier to send each supplier their own
spreadsheet. And then remerge the data when it is sent back. Hence
the reason I need 833 different sheets...
I know...what a pain. Although, at least I realized there had to
be a quicker way then doing it maual (talk about a nightmare).
ugh...
Thanks for any help in advance!!!
Hillary
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/