Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    May 2007
    Location
    Butler, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CALL DATA (EXCEL 2003)

    I have a workbook with numerous sheets. I want to call data on all the sheets at the same time by a column that varies sheet to sheet but is labeled by sales rep name so I would need a pop up to let me type in the name. I then need all the called data to be dumped into another workbook onto corresponding spreadsheets. The purpose of this is to send to each sales rep the data that belongs to them which is maintained on a master spreadsheet. I have 7 reps and must do this each month for each rep.

    Thanks
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    Without any data in the worksheets, there is nothing to test with and it is very difficult to determine exactly what you want to do. Could you add dummy data that could be used for testing?
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    May 2007
    Location
    Butler, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    I have attached the spreadsheet with dummy data in it. The farthest right columns have [x's] under the sales rep names as to who should receive the data, as you can see sometimes a line item must go to 2 [possibly more] individuals. So essentially wha I do now is filter on the salesrep column that I am working on, copy the data and paste it into another spreadsheet. I follow this process for each worksheet [usually 5 min in each book] and then I do this for each sales rep [7 at this time]. So what I am looking to do is call all the data [all the worksheets] for a given sales rep by the column headed with their name [remembering that on each worksheet the column number may not be the same number for the rep] and call the rows that contain the 'x' under that salesrep.
    Attached Files Attached Files

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    I am working on this, but a solution is going to take a little time.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    May 2007
    Location
    Butler, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    Thanks
    any help is greatly appreciated

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    OK, here is my try at what you are asking.

    The attached workbook contains a macro named CreateCallData. When run, this macro will create a new workbook in the same directory with the master workbook for each of the sales reps. These workbooks will contain the data for the reps. The following conditions must be met for the macro to work properly.

    1- The sales rep names must be unique since the workbooks are named the same as the sales rep.

    2- Before the macro is run, any previously created workbooks for the sales reps must have been deleted or removed from the same directory with the master workbook. The code can be changed to delete old files if desired.

    3- The last data column on each sheet in the master before the columns that indicate which reps get what data must have "Sales Rep" in row 1.
    Attached Files Attached Files
    Legare Coleman

  7. #7
    New Lounger
    Join Date
    May 2007
    Location
    Butler, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    that is just too awesome

    Thanks for all your efforts

  8. #8
    New Lounger
    Join Date
    May 2007
    Location
    Butler, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    I did some testing and am finding that data is missing. The one I tested only brought in 109 rows out of 417. I've looked at the code [pasted in below] and I cannot see where it is looking for the 'x' in each column for each rep. I have pasted in the excel spreadsheet with data to help. I had to zip it as it was too big, I did this after trying to reduce the size by eliminating data.

    Thanks


    Option Explicit


    Public Sub CreateCallData()
    Dim I As Long, lMaxCol As Long, lFRep As Long, lSheetsSave As Long, J As Long, lMaxRow As Long
    Dim lCRep As Long, K As Long
    Dim oFWS As Worksheet, oWS As Worksheet, oNWB As Workbook, oWB As Workbook
    Dim oNWS As Worksheet, oOWS As Worksheet
    Dim strRep As String, strPath As String, strNWB As String
    Application.ScreenUpdating = False
    lSheetsSave = Application.SheetsInNewWorkbook
    Set oWB = ActiveWorkbook
    Set oFWS = oWB.Worksheets(1)
    strPath = oWB.Path
    If strPath = "" Then
    MsgBox "Active workbook has not been saved."
    Exit Sub
    End If
    If Right(strPath, 1) <> "" Then
    strPath = strPath & ""
    End If
    lMaxCol = oFWS.Range("IV1").End(xlToLeft).Column - 1
    For I = lMaxCol To 0 Step -1
    If oFWS.Range("A1").Offset(0, I).Value = "Sales Rep" Then Exit For
    Next I
    If I < 0 Then
    MsgBox "Could not find Sales Rep column on sheet " & oFWS.Name
    Exit Sub
    End If
    lFRep = I + 1
    For I = lFRep To lMaxCol
    strRep = oFWS.Range("A1").Offset(0, I).Value
    strNWB = Dir(strPath & strRep & ".xls")
    If strNWB <> "" Then
    MsgBox "Workbook " & strPath & strRep & ".xls already exists"
    Exit Sub
    End If
    Next I
    For I = lFRep To lMaxCol
    strRep = oFWS.Range("A1").Offset(0, I).Value
    lCRep = I
    Application.SheetsInNewWorkbook = 1
    Set oNWB = Workbooks.Add
    Application.SheetsInNewWorkbook = lSheetsSave
    Set oNWS = oNWB.Worksheets(1)
    For Each oOWS In oWB.Worksheets
    For lCRep = 0 To oOWS.Range("IV1").End(xlToLeft).Column - 1
    If oOWS.Range("A1").Offset(0, lCRep).Value = strRep Then Exit For
    Next lCRep
    oNWS.Name = oOWS.Name
    oOWS.Cells.Copy
    oNWS.Range("A1").PasteSpecial Paste:=xlPasteFormats
    oOWS.Range("A1").EntireRow.Copy
    oNWS.Paste Destination:=oNWS.Range("A1")
    oNWS.Range("A1").Select
    If lCRep <= oOWS.Range("IV1").End(xlToLeft).Column - 1 Then
    J = 1
    K = 1
    Do While oOWS.Range("A1").Offset(J, 0).Value <> ""
    If oOWS.Range("A1").Offset(J, lCRep).Value <> "" Then
    oOWS.Range("A1").Offset(J, 0).EntireRow.Copy
    oNWS.Paste Destination:=oNWS.Range("A1").Offset(K, 0)
    K = K + 1
    End If
    J = J + 1
    Loop
    End If
    If oOWS.Index < oWB.Worksheets.Count Then
    Set oNWS = oNWB.Worksheets.Add(After:=oNWB.Worksheets(oNWB.Wo rksheets.Count))
    End If
    Next oOWS
    oNWB.Worksheets(1).Activate
    oNWB.SaveAs Filename:=strPath & strRep & ".xls"
    oNWB.Close
    Next I
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    The problem is that the macro counted on there not being any empty cells in column A in any of the worksheets. That was true in the sample you originally sent, but is not true in this file. I have changed the macro to use the Sales Rep column instead. That seems to work on this workbook. The change is in the attached file.
    Attached Files Attached Files
    Legare Coleman

  10. #10
    New Lounger
    Join Date
    May 2007
    Location
    Butler, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    I know your probbably tired of this but the last 3 worksheets [canadian, canadian index and export-SAP maintained] of the book do not populate any data. The worksheets appear for the reps but no data is contained on them. I saw that the first column said acct # instead of SAP # or ship to # so I changed that and still nothing. I did a copy and paste of the Sales Rep column header from one of the other sheets to the 3 that are not working thinking that may be a problem and still nothing.

    Help?

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    The problem is another difference between the first sample workbook and this one that I did not notice until now. There are sales reps on the last three worksheets that do not appear on the first worksheet, and that causes the code a major problem. I will basically have to rewrite the macro, so it will take a little time. I will hopefully get back to you later today.
    Legare Coleman

  12. #12
    New Lounger
    Join Date
    May 2007
    Location
    Butler, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    As our business changes so do sales reps at which time, depending on the situation, either add a column for the 'x' or change the name of the already existing column. I truly appreciate all your efforts. Thanks

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CALL DATA (EXCEL 2003)

    See if the macro in the attached workbook works better for you.
    Attached Files Attached Files
    Legare Coleman

Posting Permissions

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