I want to know if it is possible to do the following:

I have a worksheet with hours worked for approx 400 employees. As employees
request a look at their hours, I copy their individual data into a template,
which I then email to them. The template has a small pivot table, which I
have set up to auto-refresh as data in the cells is changed. I want to try
to automate this process as much as possible:

I want to copy the rows for an employee into this template (using "paste
special" and "values" only, to preserve the formatting of this template).
The selected rows would depend on the employee number, which is the first
column of the main workbook. Then I want to save it with the employee number
as the file name. Then, it would repeat this process for each employee.
Each employee's data would have to be copied into the "blank" template before
saving, since some employees might have fewer rows than the previous
employee.

Hi

Maybe in one step (with mail code)
http://www.rondebruin.nl/mail/folder3/row2.htm

See also this page
http://www.rondebruin.nl/copy5.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"richzip" <(E-Mail Removed)> wrote in message news:182AFBBC-A2EF-400D-BD2D-(E-Mail Removed)...
>I want to know if it is possible to do the following:
>
> I have a worksheet with hours worked for approx 400 employees. As employees
> request a look at their hours, I copy their individual data into a template,
> which I then email to them. The template has a small pivot table, which I
> have set up to auto-refresh as data in the cells is changed. I want to try
> to automate this process as much as possible:
>
> I want to copy the rows for an employee into this template (using "paste
> special" and "values" only, to preserve the formatting of this template).
> The selected rows would depend on the employee number, which is the first
> column of the main workbook. Then I want to save it with the employee number
> as the file name. Then, it would repeat this process for each employee.
> Each employee's data would have to be copied into the "blank" template before
> saving, since some employees might have fewer rows than the previous
> employee.
>

Hi there ..thank you so much for your help. I have a couple of other
questions:

I probalby won't use the mail thing you mentioned, since names are not on
the source workbook. However, copying the data to a new work book will be
very helpful.

I used the section titled "Create a new workbook for all unique values" and
it worked great. however, I want the source data to be copied to a
"template" in a totally separate workbook,that I already have saved in "my
document". that template has its own header row, and I want to paste the
data to the row right below that header (row 3).

Should I modify the "Create a new workbook for all unique values" or the Add
data to an existing sheet (AutoFilter))" instructions? If so, what needs to
be modified?

Thanks again!!


"Ron de Bruin" wrote:

> Hi
>
> Maybe in one step (with mail code)
> http://www.rondebruin.nl/mail/folder3/row2.htm
>
> See also this page
> http://www.rondebruin.nl/copy5.htm
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "richzip" <(E-Mail Removed)> wrote in message news:182AFBBC-A2EF-400D-BD2D-(E-Mail Removed)...
> >I want to know if it is possible to do the following:
> >
> > I have a worksheet with hours worked for approx 400 employees. As employees
> > request a look at their hours, I copy their individual data into a template,
> > which I then email to them. The template has a small pivot table, which I
> > have set up to auto-refresh as data in the cells is changed. I want to try
> > to automate this process as much as possible:
> >
> > I want to copy the rows for an employee into this template (using "paste
> > special" and "values" only, to preserve the formatting of this template).
> > The selected rows would depend on the employee number, which is the first
> > column of the main workbook. Then I want to save it with the employee number
> > as the file name. Then, it would repeat this process for each employee.
> > Each employee's data would have to be copied into the "blank" template before
> > saving, since some employees might have fewer rows than the previous
> > employee.
> >
>

Hi richzip

Is it a normal workbook the template or a real template workbook (xlt)


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"richzip" <(E-Mail Removed)> wrote in message news:7DB74C38-CA0A-4B4E-A903-(E-Mail Removed)...
> Hi there ..thank you so much for your help. I have a couple of other
> questions:
>
> I probalby won't use the mail thing you mentioned, since names are not on
> the source workbook. However, copying the data to a new work book will be
> very helpful.
>
> I used the section titled "Create a new workbook for all unique values" and
> it worked great. however, I want the source data to be copied to a
> "template" in a totally separate workbook,that I already have saved in "my
> document". that template has its own header row, and I want to paste the
> data to the row right below that header (row 3).
>
> Should I modify the "Create a new workbook for all unique values" or the Add
> data to an existing sheet (AutoFilter))" instructions? If so, what needs to
> be modified?
>
> Thanks again!!
>
>
> "Ron de Bruin" wrote:
>
>> Hi
>>
>> Maybe in one step (with mail code)
>> http://www.rondebruin.nl/mail/folder3/row2.htm
>>
>> See also this page
>> http://www.rondebruin.nl/copy5.htm
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "richzip" <(E-Mail Removed)> wrote in message news:182AFBBC-A2EF-400D-BD2D-(E-Mail Removed)...
>> >I want to know if it is possible to do the following:
>> >
>> > I have a worksheet with hours worked for approx 400 employees. As employees
>> > request a look at their hours, I copy their individual data into a template,
>> > which I then email to them. The template has a small pivot table, which I
>> > have set up to auto-refresh as data in the cells is changed. I want to try
>> > to automate this process as much as possible:
>> >
>> > I want to copy the rows for an employee into this template (using "paste
>> > special" and "values" only, to preserve the formatting of this template).
>> > The selected rows would depend on the employee number, which is the first
>> > column of the main workbook. Then I want to save it with the employee number
>> > as the file name. Then, it would repeat this process for each employee.
>> > Each employee's data would have to be copied into the "blank" template before
>> > saving, since some employees might have fewer rows than the previous
>> > employee.
>> >
>>

It has a .xls file name, so I'm guessing a normal workbook. It has 2
worksheets: "paysheet" (the one I want to paste the data to) and another
one, which is used to help calculate a pivot table that is contained within
"paysheet"

"Ron de Bruin" wrote:

> Hi richzip
>
> Is it a normal workbook the template or a real template workbook (xlt)
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "richzip" <(E-Mail Removed)> wrote in message news:7DB74C38-CA0A-4B4E-A903-(E-Mail Removed)...
> > Hi there ..thank you so much for your help. I have a couple of other
> > questions:
> >
> > I probalby won't use the mail thing you mentioned, since names are not on
> > the source workbook. However, copying the data to a new work book will be
> > very helpful.
> >
> > I used the section titled "Create a new workbook for all unique values" and
> > it worked great. however, I want the source data to be copied to a
> > "template" in a totally separate workbook,that I already have saved in "my
> > document". that template has its own header row, and I want to paste the
> > data to the row right below that header (row 3).
> >
> > Should I modify the "Create a new workbook for all unique values" or the Add
> > data to an existing sheet (AutoFilter))" instructions? If so, what needs to
> > be modified?
> >
> > Thanks again!!
> >
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi
> >>
> >> Maybe in one step (with mail code)
> >> http://www.rondebruin.nl/mail/folder3/row2.htm
> >>
> >> See also this page
> >> http://www.rondebruin.nl/copy5.htm
> >>
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl/tips.htm
> >>
> >>
> >> "richzip" <(E-Mail Removed)> wrote in message news:182AFBBC-A2EF-400D-BD2D-(E-Mail Removed)...
> >> >I want to know if it is possible to do the following:
> >> >
> >> > I have a worksheet with hours worked for approx 400 employees. As employees
> >> > request a look at their hours, I copy their individual data into a template,
> >> > which I then email to them. The template has a small pivot table, which I
> >> > have set up to auto-refresh as data in the cells is changed. I want to try
> >> > to automate this process as much as possible:
> >> >
> >> > I want to copy the rows for an employee into this template (using "paste
> >> > special" and "values" only, to preserve the formatting of this template).
> >> > The selected rows would depend on the employee number, which is the first
> >> > column of the main workbook. Then I want to save it with the employee number
> >> > as the file name. Then, it would repeat this process for each employee.
> >> > Each employee's data would have to be copied into the "blank" template before
> >> > saving, since some employees might have fewer rows than the previous
> >> > employee.
> >> >
> >>
>

Test this one

Change the path here
Set WBNew = Workbooks.Open("C:\Users\Ron\Documents\mytemplate.xls")

And the paste cell here
With WBNew.Sheets("paysheet").Range("A3")



Sub Copy_To_Workbooks()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim WBNew As Workbook
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim foldername As String
Dim MyPath As String
Dim FieldNum As Integer
Dim FileExtStr As String
Dim FileFormatNum As Long

'Name of the sheet with your data
Set ws1 = Sheets("Sheet1") '<<< Change

'Determine the Excel version and file extension/format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
If ws1.Parent.FileFormat = 56 Then
FileExtStr = ".xls": FileFormatNum = 56
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
End If

'Set filter range : A1 is the top left cell of your filter range and
'the header of the first column, D is the last column in the filter range
Set rng = ws1.Range("A1" & Rows.Count)

'Set Field number of the filter column
'This example filters on the first field in the range(change the field if needed)
'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
FieldNum = 1

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' Add worksheet to copy/Paste the unique list
Set ws2 = Worksheets.Add

'Fill in the path\folder where you want the new folder with the files
'you can use also this "C:\Users\Ron\test"
MyPath = Application.DefaultFilePath

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'Create folder for the new files
foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
MkDir foldername

With ws2
'first we copy the Unique data from the filter field to ws2
rng.Columns(FieldNum).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("A1"), Unique:=True

'loop through the unique list in ws2 and filter/copy to a new workbook
Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In .Range("A2:A" & Lrow)

Set WBNew = Workbooks.Open("C:\Users\Ron\Documents\mytemplate.xls")

'Firstly, remove the AutoFilter
ws1.AutoFilterMode = False

'Filter the range
rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value

'Copy the visible data and use PasteSpecial to paste to the new worksheet
ws1.AutoFilter.Range.Copy
With WBNew.Sheets("paysheet").Range("A3")
.Parent.Select
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

'Save the file in the new folder and close it
WBNew.SaveAs foldername & " Value = " _
& cell.Value & FileExtStr, FileFormatNum
WBNew.Close False

'Close AutoFilter
ws1.AutoFilterMode = False

Next cell

'Delete the ws2 sheet
On Error Resume Next
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
On Error GoTo 0

End With

MsgBox "Look in " & foldername & " for the files"

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"richzip" <(E-Mail Removed)> wrote in message news:E9539631-CDEC-4349-B687-(E-Mail Removed)...
> It has a .xls file name, so I'm guessing a normal workbook. It has 2
> worksheets: "paysheet" (the one I want to paste the data to) and another
> one, which is used to help calculate a pivot table that is contained within
> "paysheet"
>
> "Ron de Bruin" wrote:
>
>> Hi richzip
>>
>> Is it a normal workbook the template or a real template workbook (xlt)
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "richzip" <(E-Mail Removed)> wrote in message news:7DB74C38-CA0A-4B4E-A903-(E-Mail Removed)...
>> > Hi there ..thank you so much for your help. I have a couple of other
>> > questions:
>> >
>> > I probalby won't use the mail thing you mentioned, since names are not on
>> > the source workbook. However, copying the data to a new work book will be
>> > very helpful.
>> >
>> > I used the section titled "Create a new workbook for all unique values" and
>> > it worked great. however, I want the source data to be copied to a
>> > "template" in a totally separate workbook,that I already have saved in "my
>> > document". that template has its own header row, and I want to paste the
>> > data to the row right below that header (row 3).
>> >
>> > Should I modify the "Create a new workbook for all unique values" or the Add
>> > data to an existing sheet (AutoFilter))" instructions? If so, what needs to
>> > be modified?
>> >
>> > Thanks again!!
>> >
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi
>> >>
>> >> Maybe in one step (with mail code)
>> >> http://www.rondebruin.nl/mail/folder3/row2.htm
>> >>
>> >> See also this page
>> >> http://www.rondebruin.nl/copy5.htm
>> >>
>> >>
>> >> --
>> >>
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl/tips.htm
>> >>
>> >>
>> >> "richzip" <(E-Mail Removed)> wrote in message news:182AFBBC-A2EF-400D-BD2D-(E-Mail Removed)...
>> >> >I want to know if it is possible to do the following:
>> >> >
>> >> > I have a worksheet with hours worked for approx 400 employees. As employees
>> >> > request a look at their hours, I copy their individual data into a template,
>> >> > which I then email to them. The template has a small pivot table, which I
>> >> > have set up to auto-refresh as data in the cells is changed. I want to try
>> >> > to automate this process as much as possible:
>> >> >
>> >> > I want to copy the rows for an employee into this template (using "paste
>> >> > special" and "values" only, to preserve the formatting of this template).
>> >> > The selected rows would depend on the employee number, which is the first
>> >> > column of the main workbook. Then I want to save it with the employee number
>> >> > as the file name. Then, it would repeat this process for each employee.
>> >> > Each employee's data would have to be copied into the "blank" template before
>> >> > saving, since some employees might have fewer rows than the previous
>> >> > employee.
>> >> >
>> >>
>>

Hi Ron,

When I tried to run this macro, I got an error window that simply said
"400". It did the auto-filter, and filtered to show my first ID # from the
source workbook. It also created a 2nd worksheet that listed all the ID
numbers from the source worksheet. It also opened the destination workbook I
wanted to paste the data too, and the cursor went to the cell where I wanted
the paste to start. That was it.

Thanks, Rich

"Ron de Bruin" wrote:

> Test this one
>
> Change the path here
> Set WBNew = Workbooks.Open("C:\Users\Ron\Documents\mytemplate.xls")
>
> And the paste cell here
> With WBNew.Sheets("paysheet").Range("A3")
>
>
>
> Sub Copy_To_Workbooks()
> Dim CalcMode As Long
> Dim ws1 As Worksheet
> Dim ws2 As Worksheet
> Dim WBNew As Workbook
> Dim rng As Range
> Dim cell As Range
> Dim Lrow As Long
> Dim foldername As String
> Dim MyPath As String
> Dim FieldNum As Integer
> Dim FileExtStr As String
> Dim FileFormatNum As Long
>
> 'Name of the sheet with your data
> Set ws1 = Sheets("Sheet1") '<<< Change
>
> 'Determine the Excel version and file extension/format
> If Val(Application.Version) < 12 Then
> 'You use Excel 97-2003
> FileExtStr = ".xls": FileFormatNum = -4143
> Else
> 'You use Excel 2007
> If ws1.Parent.FileFormat = 56 Then
> FileExtStr = ".xls": FileFormatNum = 56
> Else
> FileExtStr = ".xlsx": FileFormatNum = 51
> End If
> End If
>
> 'Set filter range : A1 is the top left cell of your filter range and
> 'the header of the first column, D is the last column in the filter range
> Set rng = ws1.Range("A1" & Rows.Count)
>
> 'Set Field number of the filter column
> 'This example filters on the first field in the range(change the field if needed)
> 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
> FieldNum = 1
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> ' Add worksheet to copy/Paste the unique list
> Set ws2 = Worksheets.Add
>
> 'Fill in the path\folder where you want the new folder with the files
> 'you can use also this "C:\Users\Ron\test"
> MyPath = Application.DefaultFilePath
>
> 'Add a slash at the end if the user forget it
> If Right(MyPath, 1) <> "\" Then
> MyPath = MyPath & "\"
> End If
>
> 'Create folder for the new files
> foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
> MkDir foldername
>
> With ws2
> 'first we copy the Unique data from the filter field to ws2
> rng.Columns(FieldNum).AdvancedFilter _
> Action:=xlFilterCopy, _
> CopyToRange:=.Range("A1"), Unique:=True
>
> 'loop through the unique list in ws2 and filter/copy to a new workbook
> Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
> For Each cell In .Range("A2:A" & Lrow)
>
> Set WBNew = Workbooks.Open("C:\Users\Ron\Documents\mytemplate.xls")
>
> 'Firstly, remove the AutoFilter
> ws1.AutoFilterMode = False
>
> 'Filter the range
> rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value
>
> 'Copy the visible data and use PasteSpecial to paste to the new worksheet
> ws1.AutoFilter.Range.Copy
> With WBNew.Sheets("paysheet").Range("A3")
> .Parent.Select
> ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
> .PasteSpecial Paste:=8
> .PasteSpecial xlPasteValues
> .PasteSpecial xlPasteFormats
> Application.CutCopyMode = False
> .Select
> End With
>
> 'Save the file in the new folder and close it
> WBNew.SaveAs foldername & " Value = " _
> & cell.Value & FileExtStr, FileFormatNum
> WBNew.Close False
>
> 'Close AutoFilter
> ws1.AutoFilterMode = False
>
> Next cell
>
> 'Delete the ws2 sheet
> On Error Resume Next
> Application.DisplayAlerts = False
> .Delete
> Application.DisplayAlerts = True
> On Error GoTo 0
>
> End With
>
> MsgBox "Look in " & foldername & " for the files"
>
> With Application
> .ScreenUpdating = True
> .Calculation = CalcMode
> End With
> End Sub
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "richzip" <(E-Mail Removed)> wrote in message news:E9539631-CDEC-4349-B687-(E-Mail Removed)...
> > It has a .xls file name, so I'm guessing a normal workbook. It has 2
> > worksheets: "paysheet" (the one I want to paste the data to) and another
> > one, which is used to help calculate a pivot table that is contained within
> > "paysheet"
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi richzip
> >>
> >> Is it a normal workbook the template or a real template workbook (xlt)
> >>
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl/tips.htm
> >>
> >>
> >> "richzip" <(E-Mail Removed)> wrote in message news:7DB74C38-CA0A-4B4E-A903-(E-Mail Removed)...
> >> > Hi there ..thank you so much for your help. I have a couple of other
> >> > questions:
> >> >
> >> > I probalby won't use the mail thing you mentioned, since names are not on
> >> > the source workbook. However, copying the data to a new work book will be
> >> > very helpful.
> >> >
> >> > I used the section titled "Create a new workbook for all unique values" and
> >> > it worked great. however, I want the source data to be copied to a
> >> > "template" in a totally separate workbook,that I already have saved in "my
> >> > document". that template has its own header row, and I want to paste the
> >> > data to the row right below that header (row 3).
> >> >
> >> > Should I modify the "Create a new workbook for all unique values" or the Add
> >> > data to an existing sheet (AutoFilter))" instructions? If so, what needs to
> >> > be modified?
> >> >
> >> > Thanks again!!
> >> >
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> Maybe in one step (with mail code)
> >> >> http://www.rondebruin.nl/mail/folder3/row2.htm
> >> >>
> >> >> See also this page
> >> >> http://www.rondebruin.nl/copy5.htm
> >> >>
> >> >>
> >> >> --
> >> >>
> >> >> Regards Ron de Bruin
> >> >> http://www.rondebruin.nl/tips.htm
> >> >>
> >> >>
> >> >> "richzip" <(E-Mail Removed)> wrote in message news:182AFBBC-A2EF-400D-BD2D-(E-Mail Removed)...
> >> >> >I want to know if it is possible to do the following:
> >> >> >
> >> >> > I have a worksheet with hours worked for approx 400 employees. As employees
> >> >> > request a look at their hours, I copy their individual data into a template,
> >> >> > which I then email to them. The template has a small pivot table, which I
> >> >> > have set up to auto-refresh as data in the cells is changed. I want to try
> >> >> > to automate this process as much as possible:
> >> >> >
> >> >> > I want to copy the rows for an employee into this template (using "paste
> >> >> > special" and "values" only, to preserve the formatting of this template).
> >> >> > The selected rows would depend on the employee number, which is the first
> >> >> > column of the main workbook. Then I want to save it with the employee number
> >> >> > as the file name. Then, it would repeat this process for each employee.
> >> >> > Each employee's data would have to be copied into the "blank" template before
> >> >> > saving, since some employees might have fewer rows than the previous
> >> >> > employee.
> >> >> >
> >> >>
> >>
>

Be sure that sheet name is correct in your template workbook (paysheet)
Is the sheet protected ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"richzip" <(E-Mail Removed)> wrote in message news:33379B06-BDD7-4A66-B0C4-(E-Mail Removed)...
> Hi Ron,
>
> When I tried to run this macro, I got an error window that simply said
> "400". It did the auto-filter, and filtered to show my first ID # from the
> source workbook. It also created a 2nd worksheet that listed all the ID
> numbers from the source worksheet. It also opened the destination workbook I
> wanted to paste the data too, and the cursor went to the cell where I wanted
> the paste to start. That was it.
>
> Thanks, Rich
>
> "Ron de Bruin" wrote:
>
>> Test this one
>>
>> Change the path here
>> Set WBNew = Workbooks.Open("C:\Users\Ron\Documents\mytemplate.xls")
>>
>> And the paste cell here
>> With WBNew.Sheets("paysheet").Range("A3")
>>
>>
>>
>> Sub Copy_To_Workbooks()
>> Dim CalcMode As Long
>> Dim ws1 As Worksheet
>> Dim ws2 As Worksheet
>> Dim WBNew As Workbook
>> Dim rng As Range
>> Dim cell As Range
>> Dim Lrow As Long
>> Dim foldername As String
>> Dim MyPath As String
>> Dim FieldNum As Integer
>> Dim FileExtStr As String
>> Dim FileFormatNum As Long
>>
>> 'Name of the sheet with your data
>> Set ws1 = Sheets("Sheet1") '<<< Change
>>
>> 'Determine the Excel version and file extension/format
>> If Val(Application.Version) < 12 Then
>> 'You use Excel 97-2003
>> FileExtStr = ".xls": FileFormatNum = -4143
>> Else
>> 'You use Excel 2007
>> If ws1.Parent.FileFormat = 56 Then
>> FileExtStr = ".xls": FileFormatNum = 56
>> Else
>> FileExtStr = ".xlsx": FileFormatNum = 51
>> End If
>> End If
>>
>> 'Set filter range : A1 is the top left cell of your filter range and
>> 'the header of the first column, D is the last column in the filter range
>> Set rng = ws1.Range("A1" & Rows.Count)
>>
>> 'Set Field number of the filter column
>> 'This example filters on the first field in the range(change the field if needed)
>> 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
>> FieldNum = 1
>>
>> With Application
>> CalcMode = .Calculation
>> .Calculation = xlCalculationManual
>> .ScreenUpdating = False
>> End With
>>
>> ' Add worksheet to copy/Paste the unique list
>> Set ws2 = Worksheets.Add
>>
>> 'Fill in the path\folder where you want the new folder with the files
>> 'you can use also this "C:\Users\Ron\test"
>> MyPath = Application.DefaultFilePath
>>
>> 'Add a slash at the end if the user forget it
>> If Right(MyPath, 1) <> "\" Then
>> MyPath = MyPath & "\"
>> End If
>>
>> 'Create folder for the new files
>> foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
>> MkDir foldername
>>
>> With ws2
>> 'first we copy the Unique data from the filter field to ws2
>> rng.Columns(FieldNum).AdvancedFilter _
>> Action:=xlFilterCopy, _
>> CopyToRange:=.Range("A1"), Unique:=True
>>
>> 'loop through the unique list in ws2 and filter/copy to a new workbook
>> Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
>> For Each cell In .Range("A2:A" & Lrow)
>>
>> Set WBNew = Workbooks.Open("C:\Users\Ron\Documents\mytemplate.xls")
>>
>> 'Firstly, remove the AutoFilter
>> ws1.AutoFilterMode = False
>>
>> 'Filter the range
>> rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value
>>
>> 'Copy the visible data and use PasteSpecial to paste to the new worksheet
>> ws1.AutoFilter.Range.Copy
>> With WBNew.Sheets("paysheet").Range("A3")
>> .Parent.Select
>> ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
>> .PasteSpecial Paste:=8
>> .PasteSpecial xlPasteValues
>> .PasteSpecial xlPasteFormats
>> Application.CutCopyMode = False
>> .Select
>> End With
>>
>> 'Save the file in the new folder and close it
>> WBNew.SaveAs foldername & " Value = " _
>> & cell.Value & FileExtStr, FileFormatNum
>> WBNew.Close False
>>
>> 'Close AutoFilter
>> ws1.AutoFilterMode = False
>>
>> Next cell
>>
>> 'Delete the ws2 sheet
>> On Error Resume Next
>> Application.DisplayAlerts = False
>> .Delete
>> Application.DisplayAlerts = True
>> On Error GoTo 0
>>
>> End With
>>
>> MsgBox "Look in " & foldername & " for the files"
>>
>> With Application
>> .ScreenUpdating = True
>> .Calculation = CalcMode
>> End With
>> End Sub
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "richzip" <(E-Mail Removed)> wrote in message news:E9539631-CDEC-4349-B687-(E-Mail Removed)...
>> > It has a .xls file name, so I'm guessing a normal workbook. It has 2
>> > worksheets: "paysheet" (the one I want to paste the data to) and another
>> > one, which is used to help calculate a pivot table that is contained within
>> > "paysheet"
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi richzip
>> >>
>> >> Is it a normal workbook the template or a real template workbook (xlt)
>> >>
>> >>
>> >> --
>> >>
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl/tips.htm
>> >>
>> >>
>> >> "richzip" <(E-Mail Removed)> wrote in message news:7DB74C38-CA0A-4B4E-A903-(E-Mail Removed)...
>> >> > Hi there ..thank you so much for your help. I have a couple of other
>> >> > questions:
>> >> >
>> >> > I probalby won't use the mail thing you mentioned, since names are not on
>> >> > the source workbook. However, copying the data to a new work book will be
>> >> > very helpful.
>> >> >
>> >> > I used the section titled "Create a new workbook for all unique values" and
>> >> > it worked great. however, I want the source data to be copied to a
>> >> > "template" in a totally separate workbook,that I already have saved in "my
>> >> > document". that template has its own header row, and I want to paste the
>> >> > data to the row right below that header (row 3).
>> >> >
>> >> > Should I modify the "Create a new workbook for all unique values" or the Add
>> >> > data to an existing sheet (AutoFilter))" instructions? If so, what needs to
>> >> > be modified?
>> >> >
>> >> > Thanks again!!
>> >> >
>> >> >
>> >> > "Ron de Bruin" wrote:
>> >> >
>> >> >> Hi
>> >> >>
>> >> >> Maybe in one step (with mail code)
>> >> >> http://www.rondebruin.nl/mail/folder3/row2.htm
>> >> >>
>> >> >> See also this page
>> >> >> http://www.rondebruin.nl/copy5.htm
>> >> >>
>> >> >>
>> >> >> --
>> >> >>
>> >> >> Regards Ron de Bruin
>> >> >> http://www.rondebruin.nl/tips.htm
>> >> >>
>> >> >>
>> >> >> "richzip" <(E-Mail Removed)> wrote in message news:182AFBBC-A2EF-400D-BD2D-(E-Mail Removed)...
>> >> >> >I want to know if it is possible to do the following:
>> >> >> >
>> >> >> > I have a worksheet with hours worked for approx 400 employees. As employees
>> >> >> > request a look at their hours, I copy their individual data into a template,
>> >> >> > which I then email to them. The template has a small pivot table, which I
>> >> >> > have set up to auto-refresh as data in the cells is changed. I want to try
>> >> >> > to automate this process as much as possible:
>> >> >> >
>> >> >> > I want to copy the rows for an employee into this template (using "paste
>> >> >> > special" and "values" only, to preserve the formatting of this template).
>> >> >> > The selected rows would depend on the employee number, which is the first
>> >> >> > column of the main workbook. Then I want to save it with the employee number
>> >> >> > as the file name. Then, it would repeat this process for each employee.
>> >> >> > Each employee's data would have to be copied into the "blank" template before
>> >> >> > saving, since some employees might have fewer rows than the previous
>> >> >> > employee.
>> >> >> >
>> >> >>
>> >>
>>

No, the sheet is not protected, and the sheet name is correct.

In addition, when the macro opened the destination workbook, it
automatically "unhid" some columns I had hidden. I don't think this would
affect it, because the columns still match up to the source data. In any
case, I also tried unhiding the columns myself and re-running the macro, but
got the same error 400.

"Ron de Bruin" wrote:

> Be sure that sheet name is correct in your template workbook (paysheet)
> Is the sheet protected ?
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "richzip" <(E-Mail Removed)> wrote in message news:33379B06-BDD7-4A66-B0C4-(E-Mail Removed)...
> > Hi Ron,
> >
> > When I tried to run this macro, I got an error window that simply said
> > "400". It did the auto-filter, and filtered to show my first ID # from the
> > source workbook. It also created a 2nd worksheet that listed all the ID
> > numbers from the source worksheet. It also opened the destination workbook I
> > wanted to paste the data too, and the cursor went to the cell where I wanted
> > the paste to start. That was it.
> >
> > Thanks, Rich
> >
> > "Ron de Bruin" wrote:
> >
> >> Test this one
> >>
> >> Change the path here
> >> Set WBNew = Workbooks.Open("C:\Users\Ron\Documents\mytemplate.xls")
> >>
> >> And the paste cell here
> >> With WBNew.Sheets("paysheet").Range("A3")
> >>
> >>
> >>
> >> Sub Copy_To_Workbooks()
> >> Dim CalcMode As Long
> >> Dim ws1 As Worksheet
> >> Dim ws2 As Worksheet
> >> Dim WBNew As Workbook
> >> Dim rng As Range
> >> Dim cell As Range
> >> Dim Lrow As Long
> >> Dim foldername As String
> >> Dim MyPath As String
> >> Dim FieldNum As Integer
> >> Dim FileExtStr As String
> >> Dim FileFormatNum As Long
> >>
> >> 'Name of the sheet with your data
> >> Set ws1 = Sheets("Sheet1") '<<< Change
> >>
> >> 'Determine the Excel version and file extension/format
> >> If Val(Application.Version) < 12 Then
> >> 'You use Excel 97-2003
> >> FileExtStr = ".xls": FileFormatNum = -4143
> >> Else
> >> 'You use Excel 2007
> >> If ws1.Parent.FileFormat = 56 Then
> >> FileExtStr = ".xls": FileFormatNum = 56
> >> Else
> >> FileExtStr = ".xlsx": FileFormatNum = 51
> >> End If
> >> End If
> >>
> >> 'Set filter range : A1 is the top left cell of your filter range and
> >> 'the header of the first column, D is the last column in the filter range
> >> Set rng = ws1.Range("A1" & Rows.Count)
> >>
> >> 'Set Field number of the filter column
> >> 'This example filters on the first field in the range(change the field if needed)
> >> 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
> >> FieldNum = 1
> >>
> >> With Application
> >> CalcMode = .Calculation
> >> .Calculation = xlCalculationManual
> >> .ScreenUpdating = False
> >> End With
> >>
> >> ' Add worksheet to copy/Paste the unique list
> >> Set ws2 = Worksheets.Add
> >>
> >> 'Fill in the path\folder where you want the new folder with the files
> >> 'you can use also this "C:\Users\Ron\test"
> >> MyPath = Application.DefaultFilePath
> >>
> >> 'Add a slash at the end if the user forget it
> >> If Right(MyPath, 1) <> "\" Then
> >> MyPath = MyPath & "\"
> >> End If
> >>
> >> 'Create folder for the new files
> >> foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
> >> MkDir foldername
> >>
> >> With ws2
> >> 'first we copy the Unique data from the filter field to ws2
> >> rng.Columns(FieldNum).AdvancedFilter _
> >> Action:=xlFilterCopy, _
> >> CopyToRange:=.Range("A1"), Unique:=True
> >>
> >> 'loop through the unique list in ws2 and filter/copy to a new workbook
> >> Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
> >> For Each cell In .Range("A2:A" & Lrow)
> >>
> >> Set WBNew = Workbooks.Open("C:\Users\Ron\Documents\mytemplate.xls")
> >>
> >> 'Firstly, remove the AutoFilter
> >> ws1.AutoFilterMode = False
> >>
> >> 'Filter the range
> >> rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value
> >>
> >> 'Copy the visible data and use PasteSpecial to paste to the new worksheet
> >> ws1.AutoFilter.Range.Copy
> >> With WBNew.Sheets("paysheet").Range("A3")
> >> .Parent.Select
> >> ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
> >> .PasteSpecial Paste:=8
> >> .PasteSpecial xlPasteValues
> >> .PasteSpecial xlPasteFormats
> >> Application.CutCopyMode = False
> >> .Select
> >> End With
> >>
> >> 'Save the file in the new folder and close it
> >> WBNew.SaveAs foldername & " Value = " _
> >> & cell.Value & FileExtStr, FileFormatNum
> >> WBNew.Close False
> >>
> >> 'Close AutoFilter
> >> ws1.AutoFilterMode = False
> >>
> >> Next cell
> >>
> >> 'Delete the ws2 sheet
> >> On Error Resume Next
> >> Application.DisplayAlerts = False
> >> .Delete
> >> Application.DisplayAlerts = True
> >> On Error GoTo 0
> >>
> >> End With
> >>
> >> MsgBox "Look in " & foldername & " for the files"
> >>
> >> With Application
> >> .ScreenUpdating = True
> >> .Calculation = CalcMode
> >> End With
> >> End Sub
> >>
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl/tips.htm
> >>
> >>
> >> "richzip" <(E-Mail Removed)> wrote in message news:E9539631-CDEC-4349-B687-(E-Mail Removed)...
> >> > It has a .xls file name, so I'm guessing a normal workbook. It has 2
> >> > worksheets: "paysheet" (the one I want to paste the data to) and another
> >> > one, which is used to help calculate a pivot table that is contained within
> >> > "paysheet"
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> Hi richzip
> >> >>
> >> >> Is it a normal workbook the template or a real template workbook (xlt)
> >> >>
> >> >>
> >> >> --
> >> >>
> >> >> Regards Ron de Bruin
> >> >> http://www.rondebruin.nl/tips.htm
> >> >>
> >> >>
> >> >> "richzip" <(E-Mail Removed)> wrote in message news:7DB74C38-CA0A-4B4E-A903-(E-Mail Removed)...
> >> >> > Hi there ..thank you so much for your help. I have a couple of other
> >> >> > questions:
> >> >> >
> >> >> > I probalby won't use the mail thing you mentioned, since names are not on
> >> >> > the source workbook. However, copying the data to a new work book will be
> >> >> > very helpful.
> >> >> >
> >> >> > I used the section titled "Create a new workbook for all unique values" and
> >> >> > it worked great. however, I want the source data to be copied to a
> >> >> > "template" in a totally separate workbook,that I already have saved in "my
> >> >> > document". that template has its own header row, and I want to paste the
> >> >> > data to the row right below that header (row 3).
> >> >> >
> >> >> > Should I modify the "Create a new workbook for all unique values" or the Add
> >> >> > data to an existing sheet (AutoFilter))" instructions? If so, what needs to
> >> >> > be modified?
> >> >> >
> >> >> > Thanks again!!
> >> >> >
> >> >> >
> >> >> > "Ron de Bruin" wrote:
> >> >> >
> >> >> >> Hi
> >> >> >>
> >> >> >> Maybe in one step (with mail code)
> >> >> >> http://www.rondebruin.nl/mail/folder3/row2.htm
> >> >> >>
> >> >> >> See also this page
> >> >> >> http://www.rondebruin.nl/copy5.htm
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >>
> >> >> >> Regards Ron de Bruin
> >> >> >> http://www.rondebruin.nl/tips.htm
> >> >> >>
> >> >> >>
> >> >> >> "richzip" <(E-Mail Removed)> wrote in message news:182AFBBC-A2EF-400D-BD2D-(E-Mail Removed)...
> >> >> >> >I want to know if it is possible to do the following:
> >> >> >> >
> >> >> >> > I have a worksheet with hours worked for approx 400 employees. As employees
> >> >> >> > request a look at their hours, I copy their individual data into a template,
> >> >> >> > which I then email to them. The template has a small pivot table, which I
> >> >> >> > have set up to auto-refresh as data in the cells is changed. I want to try
> >> >> >> > to automate this process as much as possible:
> >> >> >> >
> >> >> >> > I want to copy the rows for an employee into this template (using "paste
> >> >> >> > special" and "values" only, to preserve the formatting of this template).
> >> >> >> > The selected rows would depend on the employee number, which is the first
> >> >> >> > column of the main workbook. Then I want to save it with the employee number
> >> >> >> > as the file name. Then, it would repeat this process for each employee.
> >> >> >> > Each employee's data would have to be copied into the "blank" template before
> >> >> >> > saving, since some employees might have fewer rows than the previous
> >> >> >> > employee.
> >> >> >> >
> >> >> >>
> >> >>
> >>
>

>In addition, when the macro opened the destination workbook, it
>automatically "unhid" some columns I had hidden.

This is very strange

If you test it with another template workbook do you have the same problem then ?
If you want you can send me your template workbook and I look at it for you

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"richzip" <(E-Mail Removed)> wrote in message news:E1506029-C6B9-4B63-88CA-(E-Mail Removed)...
> No, the sheet is not protected, and the sheet name is correct.
>
> In addition, when the macro opened the destination workbook, it
> automatically "unhid" some columns I had hidden. I don't think this would
> affect it, because the columns still match up to the source data. In any
> case, I also tried unhiding the columns myself and re-running the macro, but
> got the same error 400.
>
> "Ron de Bruin" wrote:
>
>> Be sure that sheet name is correct in your template workbook (paysheet)
>> Is the sheet protected ?
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "richzip" <(E-Mail Removed)> wrote in message news:33379B06-BDD7-4A66-B0C4-(E-Mail Removed)...
>> > Hi Ron,
>> >
>> > When I tried to run this macro, I got an error window that simply said
>> > "400". It did the auto-filter, and filtered to show my first ID # from the
>> > source workbook. It also created a 2nd worksheet that listed all the ID
>> > numbers from the source worksheet. It also opened the destination workbook I
>> > wanted to paste the data too, and the cursor went to the cell where I wanted
>> > the paste to start. That was it.
>> >
>> > Thanks, Rich
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Test this one
>> >>
>> >> Change the path here
>> >> Set WBNew = Workbooks.Open("C:\Users\Ron\Documents\mytemplate.xls")
>> >>
>> >> And the paste cell here
>> >> With WBNew.Sheets("paysheet").Range("A3")
>> >>
>> >>
>> >>
>> >> Sub Copy_To_Workbooks()
>> >> Dim CalcMode As Long
>> >> Dim ws1 As Worksheet
>> >> Dim ws2 As Worksheet
>> >> Dim WBNew As Workbook
>> >> Dim rng As Range
>> >> Dim cell As Range
>> >> Dim Lrow As Long
>> >> Dim foldername As String
>> >> Dim MyPath As String
>> >> Dim FieldNum As Integer
>> >> Dim FileExtStr As String
>> >> Dim FileFormatNum As Long
>> >>
>> >> 'Name of the sheet with your data
>> >> Set ws1 = Sheets("Sheet1") '<<< Change
>> >>
>> >> 'Determine the Excel version and file extension/format
>> >> If Val(Application.Version) < 12 Then
>> >> 'You use Excel 97-2003
>> >> FileExtStr = ".xls": FileFormatNum = -4143
>> >> Else
>> >> 'You use Excel 2007
>> >> If ws1.Parent.FileFormat = 56 Then
>> >> FileExtStr = ".xls": FileFormatNum = 56
>> >> Else
>> >> FileExtStr = ".xlsx": FileFormatNum = 51
>> >> End If
>> >> End If
>> >>
>> >> 'Set filter range : A1 is the top left cell of your filter range and
>> >> 'the header of the first column, D is the last column in the filter range
>> >> Set rng = ws1.Range("A1" & Rows.Count)
>> >>
>> >> 'Set Field number of the filter column
>> >> 'This example filters on the first field in the range(change the field if needed)
>> >> 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
>> >> FieldNum = 1
>> >>
>> >> With Application
>> >> CalcMode = .Calculation
>> >> .Calculation = xlCalculationManual
>> >> .ScreenUpdating = False
>> >> End With
>> >>
>> >> ' Add worksheet to copy/Paste the unique list
>> >> Set ws2 = Worksheets.Add
>> >>
>> >> 'Fill in the path\folder where you want the new folder with the files
>> >> 'you can use also this "C:\Users\Ron\test"
>> >> MyPath = Application.DefaultFilePath
>> >>
>> >> 'Add a slash at the end if the user forget it
>> >> If Right(MyPath, 1) <> "\" Then
>> >> MyPath = MyPath & "\"
>> >> End If
>> >>
>> >> 'Create folder for the new files
>> >> foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
>> >> MkDir foldername
>> >>
>> >> With ws2
>> >> 'first we copy the Unique data from the filter field to ws2
>> >> rng.Columns(FieldNum).AdvancedFilter _
>> >> Action:=xlFilterCopy, _
>> >> CopyToRange:=.Range("A1"), Unique:=True
>> >>
>> >> 'loop through the unique list in ws2 and filter/copy to a new workbook
>> >> Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
>> >> For Each cell In .Range("A2:A" & Lrow)
>> >>
>> >> Set WBNew = Workbooks.Open("C:\Users\Ron\Documents\mytemplate.xls")
>> >>
>> >> 'Firstly, remove the AutoFilter
>> >> ws1.AutoFilterMode = False
>> >>
>> >> 'Filter the range
>> >> rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value
>> >>
>> >> 'Copy the visible data and use PasteSpecial to paste to the new worksheet
>> >> ws1.AutoFilter.Range.Copy
>> >> With WBNew.Sheets("paysheet").Range("A3")
>> >> .Parent.Select
>> >> ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
>> >> .PasteSpecial Paste:=8
>> >> .PasteSpecial xlPasteValues
>> >> .PasteSpecial xlPasteFormats
>> >> Application.CutCopyMode = False
>> >> .Select
>> >> End With
>> >>
>> >> 'Save the file in the new folder and close it
>> >> WBNew.SaveAs foldername & " Value = " _
>> >> & cell.Value & FileExtStr, FileFormatNum
>> >> WBNew.Close False
>> >>
>> >> 'Close AutoFilter
>> >> ws1.AutoFilterMode = False
>> >>
>> >> Next cell
>> >>
>> >> 'Delete the ws2 sheet
>> >> On Error Resume Next
>> >> Application.DisplayAlerts = False
>> >> .Delete
>> >> Application.DisplayAlerts = True
>> >> On Error GoTo 0
>> >>
>> >> End With
>> >>
>> >> MsgBox "Look in " & foldername & " for the files"
>> >>
>> >> With Application
>> >> .ScreenUpdating = True
>> >> .Calculation = CalcMode
>> >> End With
>> >> End Sub
>> >>
>> >>
>> >> --
>> >>
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl/tips.htm
>> >>
>> >>
>> >> "richzip" <(E-Mail Removed)> wrote in message news:E9539631-CDEC-4349-B687-(E-Mail Removed)...
>> >> > It has a .xls file name, so I'm guessing a normal workbook. It has 2
>> >> > worksheets: "paysheet" (the one I want to paste the data to) and another
>> >> > one, which is used to help calculate a pivot table that is contained within
>> >> > "paysheet"
>> >> >
>> >> > "Ron de Bruin" wrote:
>> >> >
>> >> >> Hi richzip
>> >> >>
>> >> >> Is it a normal workbook the template or a real template workbook (xlt)
>> >> >>
>> >> >>
>> >> >> --
>> >> >>
>> >> >> Regards Ron de Bruin
>> >> >> http://www.rondebruin.nl/tips.htm
>> >> >>
>> >> >>
>> >> >> "richzip" <(E-Mail Removed)> wrote in message news:7DB74C38-CA0A-4B4E-A903-(E-Mail Removed)...
>> >> >> > Hi there ..thank you so much for your help. I have a couple of other
>> >> >> > questions:
>> >> >> >
>> >> >> > I probalby won't use the mail thing you mentioned, since names are not on
>> >> >> > the source workbook. However, copying the data to a new work book will be
>> >> >> > very helpful.
>> >> >> >
>> >> >> > I used the section titled "Create a new workbook for all unique values" and
>> >> >> > it worked great. however, I want the source data to be copied to a
>> >> >> > "template" in a totally separate workbook,that I already have saved in "my
>> >> >> > document". that template has its own header row, and I want to paste the
>> >> >> > data to the row right below that header (row 3).
>> >> >> >
>> >> >> > Should I modify the "Create a new workbook for all unique values" or the Add
>> >> >> > data to an existing sheet (AutoFilter))" instructions? If so, what needs to
>> >> >> > be modified?
>> >> >> >
>> >> >> > Thanks again!!
>> >> >> >
>> >> >> >
>> >> >> > "Ron de Bruin" wrote:
>> >> >> >
>> >> >> >> Hi
>> >> >> >>
>> >> >> >> Maybe in one step (with mail code)
>> >> >> >> http://www.rondebruin.nl/mail/folder3/row2.htm
>> >> >> >>
>> >> >> >> See also this page
>> >> >> >> http://www.rondebruin.nl/copy5.htm
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >> >>
>> >> >> >> Regards Ron de Bruin
>> >> >> >> http://www.rondebruin.nl/tips.htm
>> >> >> >>
>> >> >> >>
>> >> >> >> "richzip" <(E-Mail Removed)> wrote in message
>> >> >> >> news:182AFBBC-A2EF-400D-BD2D-(E-Mail Removed)...
>> >> >> >> >I want to know if it is possible to do the following:
>> >> >> >> >
>> >> >> >> > I have a worksheet with hours worked for approx 400 employees. As employees
>> >> >> >> > request a look at their hours, I copy their individual data into a template,
>> >> >> >> > which I then email to them. The template has a small pivot table, which I
>> >> >> >> > have set up to auto-refresh as data in the cells is changed. I want to try
>> >> >> >> > to automate this process as much as possible:
>> >> >> >> >
>> >> >> >> > I want to copy the rows for an employee into this template (using "paste
>> >> >> >> > special" and "values" only, to preserve the formatting of this template).
>> >> >> >> > The selected rows would depend on the employee number, which is the first
>> >> >> >> > column of the main workbook. Then I want to save it with the employee number
>> >> >> >> > as the file name. Then, it would repeat this process for each employee.
>> >> >> >> > Each employee's data would have to be copied into the "blank" template before
>> >> >> >> > saving, since some employees might have fewer rows than the previous
>> >> >> >> > employee.
>> >> >> >> >
>> >> >> >>
>> >> >>
>> >>
>>