Copying multiple cells

N

ned ludd

First of all - sorry for the title. I know what I want to do/ask but don't know how to put that question into just a few words for the title. Please read

I work in the claims department of an insurance company. We use a number of forms and letters. A total of about 30 right now. What is really slowing the adjusters down is having to open a form (all in word) or a letter (again in word), type in information on a particular person and then print it. The smart ones save each form/letter for each customer and then just modify them as needed.

I initially thought converting all the forms/letters into excel and then having a master sheet with the customers basic info which would be mirrored on each worksheet. I have done something similar in the past and it works. The problem is that you have one excel file for each customer. If that is what I have to do then I will

What I'd like though is a master sheet with the info on the customers by row (collumns would be name, address, date of birth etc.) Then all the adjuster would have to do is select the right customer (by row) and paste that info into whatever form or letter is needed. I don't know how to get those multiple cell selections to be pasted into the proper areas of each worksheet. (ie worksheet 1,cell a1 gets mirrored on worksheet 2, cell b17 and on worksheet 17, cell d78 etc etc

The only thing I can think of is to have a blank line at the top of each worksheet, the adjuster can then copy the customers line from one worksheet into that and I will mirror the info from row a to where ever else I need it. That will work but it means the adjuster will have to copy and paste from the master into each worksheet he/she needs . I know that sounds simple but, well, it won't be simple enough for some of them.

Is there anyway that I can have the adjusters select a row on sheet 1 and then have the info on that row propogate into the required cells on the remaining 30 or so worksheets? The adjuster could then just select the required worksheet and print it.

Thanks in advance.

Ned.
 
N

Ned Ludd

OK. I have figured out part of my question. The adjuster can pick one row (on sheet one) which would have the customer's name, address, date of birth etc, paste that row onto sheet 2 and then the info from sheet 2 gets mirrored where I want it for sheets 3-35.

Is there any way to simplify this even more so that the adjuster does not have to copy and paste from sheet one to sheet two? Something like just highlighting a row on sheet one and then having a button to click which would propogate the info into the required cells in each of the other sheets?

Thanks again,


Ned.
 
A

Averil Pretty

Yes, you can have macros that would select a range, copy
it and paste it into another range...

Are you able to email me ([email protected]) a
sample of your file with dummy data so I can have a look
and then I will post the response (if I can work it out)
to this newsgroup.

The other option I'm thinking is that a simple Access
database may be the way to go if you are familiar with
it. Sorry if this upsets anyone in this Excel group!!!
I've just been experimenting with Access lately :)

Let me know how you go anyway.

Regards,
Averil
-----Original Message-----
OK. I have figured out part of my question. The adjuster
Click to expand...
can pick one row (on sheet one) which would have the
customer's name, address, date of birth etc, paste that
row onto sheet 2 and then the info from sheet 2 gets
mirrored where I want it for sheets 3-35.
Is there any way to simplify this even more so that the
Click to expand...
adjuster does not have to copy and paste from sheet one to
sheet two? Something like just highlighting a row on sheet
one and then having a button to click which would
propogate the info into the required cells in each of the
other sheets?
 
N

Ned Ludd

Averil

Thank you

I don't have any of the forms/letters converted from word to excel yet. I should get a couple done later today or early next week and will then e-mail it to you

Thanks again

Ned.
 
N

Ned Ludd

I thought about using access as well but I don't know it and the staff do not have it on their computers. We'd have to buy the license for them and then I'd have to learn it.

I think excel is the way to do this for me

Ned.
 
N

Ned Ludd

Hi Kate. Thanks for the suggestion

I actually tried mail merge as I thought that that was going to be the solution. The problem is that only some fields will be propogated and others will be filled out on a one time basis in the merged document.

What happens with the merged document is that the one-time fields disappear. Also, I can't protect the word documents if I use mail merge.

You'd think Bill would have thought of these things. ;-

There is apparently a macro fix for the mail merge problems but it comes with lots of disclaimers about it only maybe working. I know nothing of macros and the "fix" seems questionable anyway. So, excel seems the way to go

Ned.
 
A

Averil Pretty

Ned,

OK This assumes that your sheets are set up as follows:

Sheet 1 is called "Customers"
Sheet 2 is called "Selected"
Sheet 3 is called "Form1"
Sheet 4 is called "Form2" etc

Customers is set up as so:
CustID Name Address1 Address2 Suburb
1 Joe Bloggs Unit 4 121 Fake Street My Town
2 John Citizen Suite 1 100 My Street Me Town

Other fields added (not enough room here)

Selected is set up as so:
CustID
Name
Address1
Address2
Suburb
State
Zip
DOB

Adjuster input 1
Adjuster input 2

Set up the form sheets cells to pick up any info from
the "Selected" sheet. In example go into "Form1" and
click in the cell B17, type an = sign and then go to
the "Selected" sheet and click in cell B1 then press
enter. Now if you change cell B1 in "Selected" sheet you
will see that if you go to "Form1" this change is updated.

In the Adjuster input areas (rename to whatever you want)
on "Selected" sheet, just have the adjuster enter any
other data like your one time fields. Then wherever these
need to be propogated in the forms link as per above.

Handy hint: For displaying the address nicely in the
forms where you may want Suburb, State and Zip to be on
one line enter =Selected!B5&" "&Selected!B6&" "&Selected!
B7 in the cell where you want it

The following macro will do the following:
1. Prompt the Adjuster to select a customer number
2. Copy the appropriate data
3. Paste it in sheet "Selected"
4. Save the Excel file as the customer name (don't forget
to change the directory)

From here have the adjuster enter any data they need to in
the Adjuster input areas in sheet "Selected", select the
appropriate form and then print it out as required.

Then you will just have to train them to only change data
in the "Selected" sheet for it to be updated in any of the
other forms.

Add a button to your "Customers" sheet and assign the
macro.

Sub CopyCust()
Dim x As Integer, nr As Integer, nc As Integer
Worksheets("Customers").Select
Range("A2").Select
nr = ActiveCell.CurrentRegion.Rows.Count
nc = ActiveCell.CurrentRegion.Columns.Count
nc = nc
x = InputBox("What customer number do you want?", "Select
Customer Number", 1)
x = x + 1
Application.ScreenUpdating = False
Range(Cells(x, 1), Cells(x, nc)).Select
Selection.Copy
Sheets("Selected").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B1").Value = Worksheets("Customers").Cells(x,
1).Value
Application.CutCopyMode = False
Application.ScreenUpdating = True
With ActiveWorkbook
Sheets("Selected").Select
.SaveAs Filename:=" C:\MyDirectory\Clients\" & _
.Worksheets("Selected").Range
("B2").Value & ".xls"
End With
End Sub

Be careful with the line wrapping here though, I think it
may have got a bit muddled when I copied and pasted.

Hope this helps!

Averil
-----Original Message-----
Averil.

Thank you.

I don't have any of the forms/letters converted from word
Click to expand...
to excel yet. I should get a couple done later today or
early next week and will then e-mail it to you.
 
V

vamosj

I had a so mewhat simular problem when working with my movie listing o
512 movie cassettes. Here is how I was able to reference all availabl
data assorted with them.

1) Set up a Customer Sheet containing all the info on the customer.

2) In the first column you will need to put the numbers 1 thru howeve
many customers you think you will be dealing with. (Think M.S. Acces
key)

3) Have an area on the top of that sheet to put in the number of th
customer you are dealing with.

4) Using this data you can now us
"LOOKUP_value,lookup_vector,result_vector" to put this info into al
your sheets. Or to make it a little easier have it on the first shee
only, then just = the cells on the other sheets to the first shee
(it's easier to have a cell reference another cell on a sheet with
than it is to keep on putting in the "LOOKUP" all over again.

5) Pat yourself on the back and go have a cup of coffee.

If you need any other assistance on this, you can email me a
[email protected]


J. Vamo
 
Top