Solved

create new worksheets and populate data

Posted on 2014-04-09
7
279 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 27

Expert Comment

by:MacroShadow
ID: 39989360
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
ID: 39992237
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 27

Expert Comment

by:MacroShadow
ID: 39996709
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Author Comment

by:valmatic
ID: 40017937
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 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 40018269
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
ID: 40105433
thanks
0
 
LVL 7

Author Closing Comment

by:valmatic
ID: 40140228
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

912 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

19 Experts available now in Live!

Get 1:1 Help Now