Pushing Excel to the Limit

[bpgolferguy]

Hi, got something that I'm wondering if it can be done....and I ask because I have NO IDEA where to start. I have a worksheet that will list a first name in A, a last name in B, and then different data all the way from C to Z. There will probably be 100-125 rows each time this worksheet is used.

It will be used for 8 different events (tournaments) and after each event, I want to send the data to another workbook to store and then clear the worksheet out and get ready for the next event. Here's the way I want to "store" the data, though...and like I said, it may be a stretch!

I am going to personally create a folder on a drive for each person who is a "member" of my group. I'm going to name each folder their first and last name (eg. JohnDoe). Inside that folder they are also each going to have a workbook entitled "stats." Now here's the tricky part....after each "event", I want to push a button that will scan the worksheet I listed above and copy the row of data (from C to Z) and paste it into each person's individual workbook in their folder. Now to make it easier, I can designate that event 1's data will paste into row 1, event 2's data into row 2, etc.

In my head I think this can be done because my worksheet will have two cells with the first and last name, which I would think the code could look at, put them together, and then know to paste the copied data into the folder and workbook named after those cells.

Does this sound doable to anyone? Or am I just dreaming? Thanks in advance!

 

[GK039]

Hello there,

Well my first thought would be: Why don't you use Access? It will be much easier to handle this.

George

Hi, got something that I'm wondering if it can be done....and I ask because I have NO IDEA where to start. I have a worksheet that will list a first name in A, a last name in B, and then different data all the way from C to Z. There will probably be 100-125 rows each time this worksheet is used.

It will be used for 8 different events (tournaments) and after each event, I want to send the data to another workbook to store and then clear the worksheet out and get ready for the next event. Here's the way I want to "store" the data, though...and like I said, it may be a stretch!

I am going to personally create a folder on a drive for each person who is a "member" of my group. I'm going to name each folder their first and last name (eg. JohnDoe). Inside that folder they are also each going to have a workbook entitled "stats." Now here's the tricky part....after each "event", I want to push a button that will scan the worksheet I listed above and copy the row of data (from C to Z) and paste it into each person's individual workbook in their folder. Now to make it easier, I can designate that event 1's data will paste into row 1, event 2's data into row 2, etc.

In my head I think this can be done because my worksheet will have two cells with the first and last name, which I would think the code could look at, put them together, and then know to paste the copied data into the folder and workbook named after those cells.

Does this sound doable to anyone? Or am I just dreaming? Thanks in advance!

 

[bpgolferguy]

I just asked a coworker, and they have something similar where they send a copy of the WORKBOOK into a specified folder for each user....if that can be done, wouldn't see why pasting into the existing workbook couldn't!

He said that they have a pathname listed in the workbook, such as f:\Members\ and then the first and last name in the cells in the worksheet are called by the code to fill in the next part, then they finish with a file name listed in the workbook in the individual folders, \2012Stats.xls.

 

[bpgolferguy]

Hello there,

Well my first thought would be: Why don't you use Access? It will be much easier to handle this.

George

George,

You have no idea how many times that has crossed my mind However, I have done so many things to this workbook to make it do exactly what I want, I can't turn back now. Also, I like excel because I am able to use its "style" factor to make some of the stuff presentable on screen...which is a big part as well. Now I have thought about putting the "nuts and bolts" stuff into Access, then exporting it to Excel when I need to "style" it, but again that would have to pose a HUGE benefit when it comes to redoing everything.

 

[tweedle]

It's not stretching Excel too much.
People do this all the time with generating HR/Personnel files that can't co-mingle.
The biggest part is likely to be the error trapping for when things may not be where it is thought they should be...

 

[bpgolferguy]

Any ideas of where to begin?

 

[tweedle]

OK, I think this will be a good start for you.
Go through the "Public Const" and set those values to your values.



Rules/Assumptions:
The First sheet of the player workbooks is assumed to receive the data.
If a Player workbook is not found, the Tourney record is skipped and left in the Tourney Workbook. Create the player workbook and re-run.
If the Tourney record has data, but no player name, it will be deleted
The player sheets allow room for headers. (Personally, I would about croak in receiving workbooks full of unlabled values with some expectation of comprehension)

[This all goes in a Module]

Option Explicit
Public Const RootPath = "C:\tmpTourney\"    'update to the director above your player names
Public Const PlayerFileName = "Stats.xlsx"
Public Const TourneySheetName = "Tourney"
Public Const HeaderRow = 0 '0 = No Header, just data
Sub SaveOff()
Dim wbTourney, wbPlayer As Workbook
Dim wsTourney, wsPlayer As Worksheet
Dim LastTourneyRow, RowIdxTourney As Long
Dim LastPlayerRow, RowIdxPlayer As Long
Dim rngTourneyData As Range
Dim Player, PlayerFile, Retval As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wbTourney = ActiveWorkbook
Set wsTourney = wbTourney.Sheets(TourneySheetName)   'Update this to your tournament sheet name
LastTourneyRow = wsTourney.Range("A200").End(xlUp).Row
Application.DisplayAlerts = False
    For RowIdxTourney = LastTourneyRow To HeaderRow Step -1
 
        Player = Range("A" & RowIdxTourney) & Range("B" & RowIdxTourney)
 
        If Player <> "" Then
            Set rngTourneyData = wsTourney.Range("C" & RowIdxTourney & ":Z" & RowIdxTourney)
            PlayerFile = RootPath & Player & "\" & PlayerFileName
            Retval = Dir(PlayerFile)    'Set Test For Player File Exists
 
            If Retval <> "" Then    'Only try to copy if Player File Exists
                Set wbPlayer = Workbooks.Open(PlayerFile)
                Set wsPlayer = wbPlayer.Sheets(1)
 
                LastPlayerRow = wsPlayer.Range("A200").End(xlUp).Row + 1
                rngTourneyData.Copy
                wsPlayer.Range("A" & LastPlayerRow).PasteSpecial xlPasteValues
 
                wbPlayer.Close True
                Set wsPlayer = Nothing
                Set wbPlayer = Nothing
                wsTourney.Rows(RowIdxTourney).Delete (xlUp)
            End If ' Player File Found Test
        Else
            wsTourney.Rows(RowIdxTourney).Delete (xlUp)
        End If ' Player Name Test
    Next RowIdxTourney
'Clear out objects
Set wsTourney = Nothing
Set wbTourney = Nothing
'Flip Application switches back
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[bpgolferguy]

How would I make this run? Can I apply it to a button?

 

[tweedle]

I would try just running through Macros first, but yes, it can be applied to a button