getting excel to create a personalized flyer

[katiapro93]

Ok, I am going to try this in a different way. I have an excel sheet which has information like name, address, etc. for all my customers. I have another excel sheet which is like a flyer and at the bottom of it, I have to enter the name of the store, address, etc. (info found on my informational excel sheet).

I have been trying to do a mail merge, but I can't get it. What is the easiest way to have excel go into my informational sheet and get the info for the first customer, place info where needed and then create another sheet and pick the information for the next customer on the list. I need it to do this until all the customers have their own personalized flyer.

Can anyone help me. I have already wasted 2 days trying everything and I need to have this done by tomorrow. At this point, I don't have any ideas left.

Example:

Info Sheet:
Rep name of store address city
David WD Store 5800 Miami
Sandra Publix 6800 Hollywood


Address Sheet:
Flyer and at the bottom
Name of Rep:
Name of Store:
Address
City:

Please help me..............

 

[jc.021286]

Are you wanting to email or snail mail these out?

I have a quick spreadsheet that for MS 2007 will convert a spreadsheet into a pdf and send it via email, updating all cells that need to be updated, etc.

let me know, PM with your email address and I will send it.
Regards,
jc

 

[katiapro93]

Unfortunately, we are wanting to mail them out or hand them out to each rep. Any ideas how I can do this? Thanks for any help.

 

[jc.021286]

Quick answer:
if you have the info sheet
Name address etc etc

I would create a spreadsheet with an offset formula to pull the data.
so for the name, =offset(Ref!A$1,Ref!$A$10,0)

this way the name would change down the rows from your Reference page by the cell # in Ref!(A10). link everything to that A10 cell and if you set it to 1 it pulls the 1st person's information,
change to 2 and it pulls the 2nd person's info
3, 4, etc.

A quick macro to change the number and then print the flier would be easy then.

jc

 

[jc.021286]

In creating something I would set a cell to be something as such.
="Hello " & offset(Ref!A1,Ref$A$10,0)
to go to my "Ref" tab and pull the name "Mr. MacCormack"

then it is simply formating and placing all information to pull addresses.

End result

Company = offset(Ref!A1,Ref$A$10,0)

Address line1 = offset(Ref!A2,Ref$A$10,0)
Address line2 = offset(Ref!A3,Ref$A$10,0)
City = ...

Greetings = "Good Morning " & ...

Body = "We are please to announce the opening of our " & ... & " in your location and would like for you to come visit at our new address " ... &"."

*** note using excel 2007 so you can go beyond the 255 character limit makes this a lot easier. Wrap text and put in your graphics and you are laughing.

Then by changing your Ref!A10 cell you can personalize any of 1,000,000 plus customers fliers.

~jc

 

[SaladProblems]

I had to do something similar not too long ago and this is a slightly modified version of the code they helped me with on here... Basically the macro starts at the top of a list, pastes that value into a cell, recalculates the sheet, prints once, and then moves onto the next value in the list until it runs out of values.

My sheet was set up to populate itself based off the value in that cell. You could just pick whichever lookup function works best for you and fill it that way.


Sub IterateValues()
Dim CpyRNG As Range, MyVal As Range
Application.ScreenUpdating = False
Set CpyRNG = Sheets("Sheet Name").Range("Z:Z").SpecialCells(xlCellTypeConstants)
For Each MyVal In CpyRNG
With Sheets("Sheet Name")
.Range("D1").Value = MyVal.Value
.Calculate
.PrintOut Copies:=1
End With
Next MyVal

Set CpyRNG = Nothing
Application.ScreenUpdating = True
End Sub