Report

Creating master spreadsheet for mailing information [Solved]

Ask a question Notsonerdy 7Posts Monday May 30, 2016Registration date June 3, 2016 Last seen - Latest answer on Jun 3, 2016 11:17AM
Hi there,
So I am creating a workbook, for work, for our marketing, and I need help. We are completely revamping what has been done before and I was told to start new.
What I'd like to do:
Have a raw data page where all the information for every client we get can go.
Drop down menues on the first column which will help separate the data into different sheets for easier mailmerge purposes.
a formula, that will take the information entered in a row, and send it to the correct sheet when the specific drop down choice has been made.
For example:
Raw data page
Code | Address | Name |
1 | 122 Avenue | Bob | <-- This data with code 1 will then be sent to page 1.
This way, rather than having 5 or 10 separate workbooks I can then have all the information entered on 1 and if I have to search for a specific name I don't have to spend hours going through previous workbooks to.
If this is possible please let me know.
    • Side note, I have already found how to make the drop down menues, I just don't know where to go from here.
See more 
Helpful
+0
moins plus
Hello Notsonerdy,

As a start point, the following code may help:-


Option Explicit
Sub CreateSheetsCopyData()

Application.ScreenUpdating = False

        Dim ar As Variant
        Dim i As Integer
        Dim LR As Long
        Dim c As Range
        Dim ws As Worksheet
        
LR = Range("A" & Rows.Count).End(xlUp).Row
ar = Sheet1.Range("A2", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp))

For Each c In Range("A2:A" & LR)
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(c.Value)
        If ws Is Nothing Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
        End If
  Next c
  
Sheet1.Select
  For i = 0 To UBound(ar)
         Sheets(ar(i, 1)).UsedRange.ClearContents
         Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, ar(i, 1)
         Range("A1", Sheet1.Range("I" & Rows.Count).End(xlUp)).Copy Sheets(ar(i, 1)).Range("A" & Rows.Count).End(xlUp)
    Next i
[A1].AutoFilter
 
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub


It would work as in the sample at the following link:-

https://www.dropbox.com/s/8ah1fh097ivsrit/Notsonerdy%28Master%20sht%20to%20multi%20shts%29.xlsm?dl=0

The code creates the sheets and transfers data to each individual sheet based on the Column A code. Click on the button to see it work.

See if this is the direction you were looking for and let us know what you think.

I hope that this helps.

Cheerio,
vcoolio.
Notsonerdy 7Posts Monday May 30, 2016Registration date June 3, 2016 Last seen - May 31, 2016 12:43AM
Thank you! :)
Reply
Add comment
Helpful
+0
moins plus
Hello Notsonerdy,

You're welcome. Glad that I could help. Come back anytime should you need any further assistance.

Just a little tip:-

If you intend to use just numbers for your code (Column A) at least place an apostrophe(') in front of them as I have done in the sample. Excel will then treat them as text as it is a little temperamental with just numbers in such code. You could also intermingle the numbers with some text.

Cheerio,
vcoolio.
Notsonerdy 7Posts Monday May 30, 2016Registration date June 3, 2016 Last seen - Jun 1, 2016 03:14PM
(Very much not a computer person)

How would I update the code to accept information to a different column (for example Column "M")? Otherwise this is exactly what I was looking for.

Thanks!
Reply
Add comment
Helpful
+0
moins plus
Hello Notsonerdy,

If you mean that you need to extend your data set out to Column M then, in line 29 of the code above, change the "I" to "M".

That should do the trick.

Cheerio,
vcoolio.
Notsonerdy 7Posts Monday May 30, 2016Registration date June 3, 2016 Last seen - Jun 1, 2016 07:09PM
So you are probably going to get annoyed with all the questions soon.

I have did what you mentioned, and changed the "I' to the "M" in line 29 to be able to extend the data to column M. However, when I did the create sheet, it created the sheets but did not transfer -any- of the data. Not the headings or any of the words/numbers entered in to the main document

I have tried resetting the code back to what you have written above and it still won't transfer any of the data.

How did I break it? And how can I fix it? LOL
Reply
Add comment
Helpful
+0
moins plus
Hello Notsonerdy,

Naughty boy(girl?)!

Upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here. Be careful with any sensitive data.

I'll have a look for you and see what the problem is.

Cheerio,
vcoolio.
Notsonerdy 7Posts Monday May 30, 2016Registration date June 3, 2016 Last seen - Jun 1, 2016 09:51PM
https://www.dropbox.com/s/08ngefl7k4lyd9s/Marketing%20Master%20Sheet.xlsm?dl=0

So I have kept all the original random data you had entered when you made the workbook. Mostly because I still wanted to play around with it before entering the actual data.

I had emailed a copy of this one to my work computer, and changed nothing except the "M" and no data transferred when the pages were created.

And for some reason, this one, on my home computer, will transfer the headers but not the data when the pages are created.

I'm excited to use one where it's all done in the script so my co-workers can't go in and fiddle with anything (They are known to delete or change formulas even after we've asked them to not touch them) And in the idea of being in a "fiddle-proof" mode, would this version of the workbook work if I made a drop down menu for the Code? Or is that more scripting? Lol

Thanks!!

PS. Doesn't really matter but yes, girl. (I am amazeballs at breaking things, horrendous at fixing them)
Reply
Add comment
Helpful
+0
moins plus
Hello Notsonerdy,

The operative word here is "Ooops". You've put "M" in the code but the data set only stretches out to "K" in the main sheet. So Excel is basically standing there with hands on hips saying:"Well, what do you want me to do?"

Create the required data columns out to "M" and you should be on the "straight and narrow".

However, maybe alter the code slightly as follows:-


Option Explicit
Sub CreateSheetsCopyData()

Application.ScreenUpdating = False

        Dim ar As Variant
        Dim i As Integer
        Dim LR As Long
        Dim c As Range
        Dim ws As Worksheet
        
LR = Range("A" & Rows.Count).End(xlUp).Row
ar = Sheet1.Range("A2", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp))

For Each c In Range("A2:A" & LR)
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(c.Value)
        If ws Is Nothing Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
        End If
  Next c
  
Sheet1.Select
  For i = 0 To UBound(ar)
         Sheets(ar(i, 1)).UsedRange.ClearContents
         Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, ar(i, 1)
         [A1].CurrentRegion.Copy Sheets(ar(i, 1)).Range("A" & Rows.Count).End(xlUp)
    Next i
[A1].AutoFilter
 
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Sheets created/data transfer completed!", vbExclamation, "Status"

End Sub


You may notice the slight change to line 29 between the two codes above. This should take care of any blank columns in the data set should you ever have any.

Following is the link to my updated test work book:-

https://www.dropbox.com/s/3odfbub0uimujgv/Notsonerdy%28Master%20sht%20to%20multi%20shts%2Cautofilter%2Ccurrentregion%29.xlsm?dl=0

 And in the idea of being in a "fiddle-proof" mode, would this version of the workbook work if I made a drop down menu for the Code? Or is that more scripting? Lol 


If you mean the "Code" column (Column A) then you would need to create a data validation drop down in each cell in Column A. Its fairly easy to do.

If you are worried about your work mates fiddling with things then it may be best to pass word protect the work book. Go to the Review tab and select "Protect Work Book". You'll be given the option to create a password.

PS. Doesn't really matter but yes, girl. (I am amazeballs at breaking things, horrendous at fixing them)


Haha. Sounds like my missus!

Good luck Notsonerdy! Come back if you get stuck.

Cheerio,
vcoolio.
Notsonerdy 7Posts Monday May 30, 2016Registration date June 3, 2016 Last seen - Jun 3, 2016 10:55AM
So everything is working perfectly (THANK YOU!)

I just noticed I seem to be having a small issue, well a large on really. I have (to start at least) over 1800 rows of data to transfer and I am finding that I am losing the button.

I know there is a way to lock the top row so it will follow whenever you scroll, is there a way to lock the button the to top row so it will scroll as well?
Reply
Notsonerdy 7Posts Monday May 30, 2016Registration date June 3, 2016 Last seen - Jun 3, 2016 11:02AM
... I just figured it out. LOL

Thanks for all your help. It's working amazingly!
Reply
Add comment
Helpful
+0
moins plus
That's most excellent, Notsonerdy!
I knew you could do it.

Good luck and don't be afraid to come back should you need any further assistance.

Cheerio,
vcoolio.
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!