Creating master spreadsheet for mailing informationSolved/Closed

Question

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

Creating master spreadsheet for mailing information

6 responses

Similar discussions

Subscribe To Our Newsletter!