Solved

create new worksheets and populate data

Posted on 2014-04-09
7
271 Views
Last Modified: 2014-06-17
Hi.  I have a spreadsheet with 1 worksheet full of customer data:  Acct No, name, address, phone, fax, etc and 1 column marked territory (north, south, central, etc...) and 1 column with the regional sales manager initials (manager for account).
Rather than take the time to create a database and move this data in I was wondering if it would be possible to grab the data in worksheet 1 and create and name a worksheet for each of my sales managers.   Worksheet1= Customer Listing, new worksheet2 = Manager1, new worksheet3 = Manager2, etc..  along with that I also want to add a heading in row 1 and add soem very basic column formatting to the new worksheet on create.  I'm thinking some sort of macro to do all of this but not sure...

Ultimately,  I want my managers to go to their section on Worksheet1, find the customer they want to update, click on it (hyperlink to corresponding sheet) and fill in call log data for visits, calls, emails, project updates, etc...)  data would be column 1= date and column 2 = call history.  Manual entry for each new call logged at that point...

thanks
0
Comment
Question by:valmatic
  • 4
  • 3
7 Comments
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
You asked several questions. Let's deal with the first one: Split data into multiple worksheets based on column.

Here you will find some code to accomplish that:
Sub parse_data()
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim icol As Long
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer
    vcol = 1
    Set ws = Sheets("Sheet1")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1:C1"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 To lr
        On Error Resume Next
        If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
            ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
        End If
    Next
    myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
    ws.Columns(icol).Clear
    For i = 2 To UBound(myarr)
        ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
        If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
            Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
        Else
            Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
        End If
        ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
        Sheets(myarr(i) & "").Columns.AutoFit
    Next
    ws.AutoFilterMode = False
    ws.Activate
End Sub

Open in new window



vcol =1, the number 1 is the column number that you want to split the data based on.
Set ws = Sheets("Sheet1"), Sheet1 is the sheet name that you want to apply this code.
title = "A1:C1", A1:C1 is the range of the title.

All of them are variables, you can change them as your need.
0
 
LVL 7

Author Comment

by:valmatic
Comment Utility
many thanks for that...  I tweaked it a little and got most of the way there.  I actually switched it up and separated each manager and their customers into their own spreadsheet and use the the 2nd column (customer name)  to title each new tab.  Lastly I'm calling another sub within the the new tab loop to add formatting to each new worksheet.  Pretty sweet..  

The only thing I need to do now is make each row of data (columnB) on sheet1 a hyperlink to open the corresponding tab that was just created.  Can we incorporate this into your code?  Not really sure how to get code to create a hyperlink to loop for each customer on sheet 1 though.   Let me know if I should create a separate question for this part.  
Range("B2").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'MUNICIPAL EQUIPMENT'!A1", TextToDisplay:="MUNICIPAL EQUIPMENT"

Open in new window


Here is the change I made to your code:
Sub CreateCallLog2()
'
' CreateCallLog2 Macro
' Create multiple sheets based on data
'

'
'Sub parse_data()
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim icol As Long
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer
    vcol = 2
    Set ws = Sheets("Sheet1")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1:B1"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 To lr
        On Error Resume Next
        If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
            ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
        End If
    Next
    myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
    ws.Columns(icol).Clear
    For i = 2 To UBound(myarr)
        ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
        If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
            Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
        Else
            Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
        End If
        ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
        Sheets(myarr(i) & "").Columns.AutoFit
        
    Call CreateCallLog1  'Formatting on new worksheets
    
    Next
    ws.AutoFilterMode = False
    ws.Activate
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Add the following line at the end of the second loop:
ws.Hyperlinks.Add Anchor:=ws.Cells(i, vcol), Address:="", SubAddress:=myarr(i) & "!B1", TextToDisplay:=""

Open in new window

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 7

Author Comment

by:valmatic
Comment Utility
Hi,  I'm so sorry I took so long to respond.  
I tried the additonal code and the hyperlink are created on sheet 1 but point back to sheet 1 cell A1.  Would it have to do with code placement?  I added at end of 2nd loop, here:
For i = 2 To UBound(myarr)
        ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
        If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
            Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
        Else
            Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
        End If
    ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
    Sheets(myarr(i) & "").Columns.AutoFit

ws.Hyperlinks.Add Anchor:=ws.Cells(i, vcol), Address:="", SubAddress:=myarr(i) & "!B1", TextToDisplay:="" 'add links on sheet1

    Call CreateCallLog1  'Formatting on new worksheets
    
    Next
    ws.AutoFilterMode = False
    ws.Activate
    
    Call CreateCallLog  'Formatting for Sheet1

Open in new window

0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
Comment Utility
Sorry, this is the right line:
ws.Hyperlinks.Add Anchor:=ws.Cells(i, vcol), Address:="", SubAddress:="'" & myarr(i) & "'" & "!B1", TextToDisplay:=ws.Cells(i, vcol).Value

Open in new window

0
 
LVL 7

Author Comment

by:valmatic
Comment Utility
thanks
0
 
LVL 7

Author Closing Comment

by:valmatic
Comment Utility
I apologize.  I had to refocus elsewhere and lost track of this case.  Please take the points and I'll post a new question if needed.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now