I am pretty new at this so I will explain what I am trying to achieve
as best as possible.
I need to send info to practices on a weekly basis with patient
information. There are 22 practices and each will receive 2 reports. I
really do not want to create 44 speadsheets every week. What I had in
mind to do was to assign a password to each record based on the
practice. Then creating a user form?? where they select their practice
and enter the password which would then show only their data. Is this
the most sensible way to do this? Is it possible? How can I do the
latter part? Many thanks -C

If you send data to someone in a workbook, then you should assume they will
have access to all the data sent regardless of any protections you apply.

The only way to insure confidentiality is to not send them data you don't
want them to see.

You question would better be - how do I write a macro that generates 44
worksheets and sends them to the appropriate email addresses.

See Ron de Bruin's site for the pieces of information you need.

http://www.rondebruin.nl/copy5.htm

http://www.rondebruin.nl/sendmail.htm

--
Regards,
Tom Ogilvy



"Ciara" wrote:

I am pretty new at this so I will explain what I am trying to achieve
as best as possible.
I need to send info to practices on a weekly basis with patient
information. There are 22 practices and each will receive 2 reports. I
really do not want to create 44 speadsheets every week. What I had in
mind to do was to assign a password to each record based on the
practice. Then creating a user form?? where they select their practice
and enter the password which would then show only their data. Is this
the most sensible way to do this? Is it possible? How can I do the
latter part? Many thanks -C

That worked really well. Thanks for you help.I think sometimes the
solution is obvious but after looking at something too long.. you
sometimes miss it. C
Tom Ogilvy wrote:
If you send data to someone in a workbook, then you should assume they will
have access to all the data sent regardless of any protections you apply.

The only way to insure confidentiality is to not send them data you don't
want them to see.

You question would better be - how do I write a macro that generates 44
worksheets and sends them to the appropriate email addresses.

See Ron de Bruin's site for the pieces of information you need.

http://www.rondebruin.nl/copy5.htm

http://www.rondebruin.nl/sendmail.htm

--
Regards,
Tom Ogilvy



"Ciara" wrote:

I am pretty new at this so I will explain what I am trying to achieve
as best as possible.
I need to send info to practices on a weekly basis with patient
information. There are 22 practices and each will receive 2 reports. I
really do not want to create 44 speadsheets every week. What I had in
mind to do was to assign a password to each record based on the
practice. Then creating a user form?? where they select their practice
and enter the password which would then show only their data. Is this
the most sensible way to do this? Is it possible? How can I do the
latter part? Many thanks -C

If I wanted to transpose the text could I replace
this: (<<<change)
CriteriaRange:=.Range("IU1:IU2"), _

CopyToRange:=WBNew.Sheets(1).Range("A1"), _ <<<change
Unique:=False <<<<change


with this:
Selection.PasteSpecial Paste:=xlAll,
Operation:=xlNone,SkipBlanks:=False ,

Transpose:=True

Or do I need to add it to it? This is using Ron's example of copying to
a new workbook.

Tom Ogilvy wrote:
If you send data to someone in a workbook, then you should assume they will
have access to all the data sent regardless of any protections you apply.

The only way to insure confidentiality is to not send them data you don't
want them to see.

You question would better be - how do I write a macro that generates 44
worksheets and sends them to the appropriate email addresses.

See Ron de Bruin's site for the pieces of information you need.

http://www.rondebruin.nl/copy5.htm

http://www.rondebruin.nl/sendmail.htm

--
Regards,
Tom Ogilvy



"Ciara" wrote:

I am pretty new at this so I will explain what I am trying to achieve
as best as possible.
I need to send info to practices on a weekly basis with patient
information. There are 22 practices and each will receive 2 reports. I
really do not want to create 44 speadsheets every week. What I had in
mind to do was to assign a password to each record based on the
practice. Then creating a user form?? where they select their practice
and enter the password which would then show only their data. Is this
the most sensible way to do this? Is it possible? How can I do the
latter part? Many thanks -C

"Ciara" wrote in message
oups.com...
I am pretty new at this so I will explain what I am trying to achieve
as best as possible.
I need to send info to practices on a weekly basis with patient
information. There are 22 practices and each will receive 2 reports. I
really do not want to create 44 speadsheets every week. What I had in
mind to do was to assign a password to each record based on the
practice. Then creating a user form?? where they select their practice
and enter the password which would then show only their data. Is this
the most sensible way to do this? Is it possible? How can I do the
latter part? Many thanks -C


1) Since you are new: Start your request with a meaningful subject. "Newbie
help pls" does not give any clue to your problem.
2) Is your solution possible? Absolutely.
3) Is it sensible? No. You are dealing with sensitive patient data. Why
would you distribute more than the recipient needs? Whatever the solution:
Put only information into the report that is relevant for your recipient.
Don't distribute unnecessary data just because you want to avoid extra work.
No matter how much you protect and hide your data with clever code: Excel is
not made to withstand serious attempts to reveal protected data.
Bottomline: Write a macro to extract necessary data for your reports from
the master data into separate workbooks.
Encrypt each report with an external encryption application. Don't rely on
Excel's password protection.

Joerg

1) Since you are new: Start your request with a meaningful subject. "Newbie
help pls" does not give any clue to your problem.
2) Is your solution possible? Absolutely.
3) Is it sensible? No. You are dealing with sensitive patient data. Why
would you distribute more than the recipient needs? Whatever the solution:
Put only information into the report that is relevant for your recipient.
Don't distribute unnecessary data just because you want to avoid extra work.
No matter how much you protect and hide your data with clever code: Excel is
not made to withstand serious attempts to reveal protected data.
Bottomline: Write a macro to extract necessary data for your reports from
the master data into separate workbooks.
Encrypt each report with an external encryption application. Don't rely on
Excel's password protection.

Joerg

Thanks for your kind response.