Results 1 to 6 of 6

Thread: getting excel to create a personalized flyer
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default getting excel to create a personalized flyer

    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..............

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    725
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting excel to create a personalized flyer

    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

  3. #3
    Board Regular
    Join Date
    Jun 2009
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting excel to create a personalized flyer

    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.

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    725
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting excel to create a personalized flyer

    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

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Posts
    725
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting excel to create a personalized flyer

    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

  6. #6
    Board Regular
    Join Date
    Apr 2010
    Posts
    279
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: getting excel to create a personalized flyer

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •