Copy To Last Row

 
Sub Transfer()

PlayerID = InputBox("Enter Player ID", "Select Player", "")
TeamID = InputBox("Enter Team ID", "Select Team", "")

Sheets("Team " & TeamID).Select

If Range("N1") < 32 Then

Sheets("Player Pool").Select
For player = 1 To 90
If Cells(plrow + 1, 1) = PlayerID Then
Range(Cells(plrow + 1, 2), Cells(plrow + 1, 9)).Cut
Sheets("Team " & TeamID).Select

For Roster = 1 To 32
If Cells(Roster + 1, 4) = "" Then
Cells(Roster + 1, 4).Select
ActiveCell.PasteSpecial
End If
Next Roster

End If

Next player

Else
Response = MsgBox("No free spots in roster")
End If

End Sub

Sub InsertSheets()
    
Application.ScreenUpdating = False

    'define variables used in script
    Dim oNewSetNum As Double
    Dim oPrevSetNum As String
    
    'look at previous sheet to determine set number
    Sheets("Project Notes").Select
    
    On Error Resume Next
    ActiveSheet.Next.Select
    
    If ActiveSheet.Name = "Project Notes" Then
        oNewSetNum = 1
    End If
    
    If ActiveSheet.Name <> "Project Notes" Then
        oPrevSetNum = Mid(ActiveSheet.Name, 8, (Len(ActiveSheet.Name) - 19))
        oNewSetNum = CDbl(oPrevSetNum) + 1
    End If
    
    'unhide template sheets
    Sheets("Sprint # Team Roster").Visible = True
    Sheets("Sprint # Backlog").Visible = True
    Sheets("Sprint # Burndown").Visible = True
    
    'copy template sheets and move to starting location
    Sheets(Array("Sprint # Team Roster", "Sprint # Backlog", "Sprint # Burndown")). _
        Copy After:=Sheets(2)
        
    'rename sheets
    Sheets("Sprint # Team Roster (2)").Name = "Sprint " & oNewSetNum & " Team Roster"
    Sheets("Sprint # Backlog (2)").Name = "Sprint " & oNewSetNum & " Backlog"
    Sheets("Sprint # Burndown (2)").Name = "Sprint " & oNewSetNum & " Burndown"
           
    'hide template sheets
    Sheets(Array("Sprint # Team Roster", "Sprint # Backlog", "Sprint # Burndown")). _
        Select
    ActiveWindow.SelectedSheets.Visible = False

    'change tab color to gray on previous set of sheets
    If oPrevSetNum >= 0 Then
        Sheets("Sprint " & oPrevSetNum & " Team Roster").Tab.ColorIndex = 15
        Sheets("Sprint " & oPrevSetNum & " Backlog").Tab.ColorIndex = 15
        Sheets("Sprint " & oPrevSetNum & " Burndown").Tab.ColorIndex = 15
    End If
    
    Sheets("Product Backlog").Select

Application.ScreenUpdating = True

End Sub

Range("###2").Select
    Selection.AutoFill Destination:=Range("###2:###2001")

Hi,
I'm new to macros and VB and believe the end solution VBA. I have attached an example file.

I desire the end result of:
Copy and paste each row from "Roster" worksheet to the other worksheets where "LO1" or "LO2" or "LO3" or "LO4" is found in the row.

For example, since row 2 contains "LO3", then this row needs copy/paste to worksheet "LO3". Since row 4 contains "LO3", it would be copy/pasted or appended to "LO3" tab underneath the row 2 that was pasted previously.

Note: It is possible each row may contain multiple location IDs but not likely.


I would like to:
- maintain the "Roster" worksheet with no changes.
- maintain the header row from "Roster" worksheet in each of the LO1..LO4 worksheets.
- maintain formatting (font, highlights) for visual aid to others viewing the worksheets.
- allow for changes to "Roster" worksheet and possibly have a button displayed to run macro after updates are completed to the Roster tab.

Any assistance is much appreciated. I haven't progressed very far with the VB code for this challenge.

Thank you.
Spooon

i've written this code to obtain a list view but i cannot visualize all
data contained in my list.
everytime i launch my macro i get an "1004 run time error", i can't
understand why.
(surely because my lack of knowledge about list view & tree view
features).

the structure i want to obtain is very simple, here follow a little
scheme

A
A1
A2
..
..
..
A14
A15
B
B1
B2
..
..
..
B14
B15

etc etc etc

Actually i can visualize only 10 of my 15 categories... is this a
limitation of list view or there's someting wrong in my code...... ???

this is what i've written

Private Sub UserForm_Initialize()
Dim player1 As String
Dim player As String

With TreeView1.Nodes
.Clear
' player1 = Worksheets("team").Range("H3").Value
player1 = "Team"
.Add , , "KEY_1", player1

For i = 4 To 25
player = Worksheets("team").Range("H" & i - 1).Value
'
' add other "father-nodes"

.Add "KEY_" & i - 3, tvwNext, "KEY_" & i - 2, player
'
' add "child nodes"

.Add "KEY_" & i - 2, tvwChild, "KEY_A" & i - 2,
Worksheets("team").Range("W2").Value & ": " &
Worksheets("team").Range("W" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_B" & i - 2,
Worksheets("team").Range("X2").Value & ": " &
Worksheets("team").Range("X" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_C" & i - 2,
Worksheets("team").Range("Y2").Value & ": " &
Worksheets("team").Range("Y" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_D" & i - 2,
Worksheets("team").Range("Z2").Value & ": " &
Worksheets("team").Range("Z" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_E" & i - 2,
Worksheets("team").Range("AA2").Value & ": " &
Worksheets("team").Range("AA" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_F" & i - 2,
Worksheets("team").Range("AB2").Value & ": " &
Worksheets("team").Range("AB" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_G" & i - 2,
Worksheets("team").Range("AC2").Value & ": " &
Worksheets("team").Range("AC" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_H" & i - 2,
Worksheets("team").Range("AD2").Value & ": " &
Worksheets("team").Range("AD" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_I" & i - 2,
Worksheets("team").Range("AE2").Value & ": " &
Worksheets("team").Range("AE" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_J" & i - 2,
Worksheets("team").Range("AF2").Value & ": " &
Worksheets("team").Range("AF" & i - 1).Value
' .Add "KEY_" & i - 2, tvwChild, "KEY_K" & i - 2,
Worksheets("team").Range("I2").Value & ": " &
Worksheets("team").Range("I " & i - 1).Value
' .Add "KEY_" & i - 2, tvwChild, "KEY_L" & i - 2,
Worksheets("team").Range("S2").Value & ": " &
Worksheets("team").Range("S" & i - 1).Value
' .Add "KEY_" & i - 2, tvwChild, "KEY_M" & i - 2,
Worksheets("team").Range("T2").Value & ": " &
Worksheets("team").Range("T" & i - 1).Value
' .Add "KEY_" & i - 2, tvwChild, "KEY_N" & i - 2,
Worksheets("team").Range("U2").Value & ": " &
Worksheets("team").Range("U" & i - 1).Value
' .Add "KEY_" & i - 2, tvwChild, "KEY_O" & i - 2,
Worksheets("team").Range("V2").Value & ": " &
Worksheets("team").Range("V" & i - 1).Value

' End If
Next i

End With

End Sub


thank for your help.

fulvio.

NB: i've other questions about this topic... but let's do it step by
step

Example.xlsxHello,
I'm trying to figure out how to organize my company's data within a workbook. I have one spreadsheet "In-process Roster" that contains in order by column the Company, Name, SSN, Position, Team, and Phone number organized by company. On the next spreadsheet I would like to make a call roster that shows Team, Name, and Phone Number. Basically I'm trying to make a call roster. What I would ultimately like to achieve is once I inprocess a new employee it will automatically update the call roster by Office. Thank you in advance.

Please,

I got some help from Peter the other day, but I still can't get it to work!
Referring to the table attached, I use vlookup to ascertain which team is home and which one is away.

But now I want to automate this process, so that when the date, division, and round number are entered, the vlookup will use say "Division 3 if I have selected that division.

And should I use the vlookup to relate the rnd no: to the date?

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF 2 No: Division 1 No: Division 2 No: Division 3 No: Division 4 3 1 Team 1 1 Team 9 1 Team 17 1 Team 25 4 2 Team 2 2 Team 10 2 Team 18 2 Team 26 5 3 Team 3 3 Team 11 3 Team 19 3 Team 27 6 4 Team 4 4 Team 12 4 Team 20 4 Team 28 7 5 Team 5 5 Team 13 5 Team 21 5 Team 29 8 6 Team 6 6 Team 14 6 Team 22 6 Team 30 9 7 Team 7 7 Team 15 7 Team 23 7 Team 31 10 8 Team 8 8 Team 16 8 Team 24 8 Team 32 11 12 13 14 01/05/10 08/05/10 13/05/10 20/05/10 27/05/10 03/06/10 10/06/10 15 Rnd 1 Rnd 2 Rnd 3 Rnd 4 Rnd 5 Rnd 6 Rnd 7 16 1 v 8 4 v 1 1 v 7 3 v 1 1 v 6 2 v 1 1 v 5 17 2 v 7 5 v 3 8 v 6 4 v 2 7 v 5 3 v 8 6 v 4 18 3 v 6 6 v 2 2 v 5 5 v 8 8 v 4 4 v 7 7 v 3 19 4 v 5 7 v 8 3 v 4 6 v 7 2 v 3 5 v 6 8 v 2 20 21 17/06/10 24/06/10 31/6/10 06/07/10 13/07/10 20/07/10 27/07/10 22 Rnd 8 Rnd 9 Rnd 10 Rnd 11 Rnd 12 Rnd 13 Rnd 14 23 8 v 1 1 v 4 7 v 1 1 v 3 6 v 1 1 v 2 5 v 1 24 7 v 2 3 v 5 6 v 8 2 v 4 5 v 7 8 v 3 4 v 6 25 6 v 3 2 v 6 5 v 2 8 v 5 4 v 8 7 v 4 3 v 7 26 5 v 4 8 v 7 4 v 3 7 v 6 3 v 2 6 v 5 2 v 8 27 28 29 30 31 32 33 Date 34 Division 35 Round Number 36 Home Team Number: 6 37 Away Team Number: 3 38 Home Team Team 6 Away Team Team 3

I have numbered and lettered the cells so you can catch my drift.
The red cells are the variables, and the green cells are where the formulas are.

Any assistance with this would be greatly appreciated!

Regards
Gavin

Hi Guys,

I have been trying to figure this one out for a while, with no joy.

I have a massive list of figures, that look something like this;

Column A - Column B - Column C - Column D
Team A - 2 - Team B - 1
Team C - 1 - Team D - 2
Team E - 0 - Team F - 3
Team G - 1 - Team H - 2
Team B - 3 - Team C - 1
Team G - 0 - Team A - 2
Team F - 1 - Team D - 0
Team H - 5 - Team E - 3
Team G - 2 - Team A - 2
Team B - 2 - Team F - 1
Team D - 0 - Team E - 1
Team C - 3 - Team H - 1
Team F - 1 - Team D - 3
Team A - 1 - Team B - 4
Team H - 0 - Team C - 1
Team E - 5 - Team G - 1

What I want to be able to do is select Home and Away, and for the last 6 home, last 6 away scores, then last 10, then last 20

For example, on the table above, if I selected Team E as Home and Team B as away, the results would be returned as;

Team E - Last - 5
Team E - 2nd Last - 0
Team E - 3rd Last - etc etc etc

Team B - Last - 4
Team B - 2nd Last - 1
Team B - 3rd Last - etc etc etc

Obviously, if the list above were longer, the

I have changed the colours in the table above to try to explain better.....

Can this be done?? Either by formula, or in VBA??

Thanks in advance

I am trying to do a European Champions League spreadsheet and i want to be able to enter the scores and the table to automaticly update, i have looked up the function Vlookup, dut do not get how to use the function.

So can anybody help me with it

the fixtures
Team A V Team B 1-1
Team C V Team D 1-0
Team B V Team D -
Team C V Team A -
Team A V Team D -
Team B V Team C -
Team C V Team B -
Team D V Team A -
Team A V Team C -
Team D V Team B -
Team B V Team A -
Team D V Team C -

I am trying to do a European Champions League spreadsheet and i want to be able to enter the scores and the table to automaticly update, i have looked up the function Vlookup, dut do not get how to use the function.

So can anybody help me with it

the fixtures
Team A V Team B 1-1
Team C V Team D 1-0
Team B V Team D -
Team C V Team A -
Team A V Team D -
Team B V Team C -
Team C V Team B -
Team D V Team A -
Team A V Team C -
Team D V Team B -
Team B V Team A -
Team D V Team C -

Dear Excel(lent) users,

I have a query concering the following.

In an excel sheet I have a number of columns, which contains teamnames (team
1...Team N)

I am using Columns A through J to indicate which team is working on an
issue. Now I want to know which is the actual current team working on the
issue.

For instance:
Issue 1 has been worked on by three teams (1,2 and 3) in the following order:
A1 = Team 1
B1 = Team 3
C1 = Team 2
In this instance Team 2 is the current team working on the issue

Another example:
A1 = Team 1
B1 = Team 5
C1 = Team 7
D1 = Team 2
E1 = Team 1
F1 = Team 3
In this instance Team 3 is the current team working on the issue

So what I am looking for is the last entry in the (horizontal) sequence.

Hope this makes sense.

--
** Fool on the hill **

Hello Excelers ,

I need one of you to help me out with this one?

Attached is a small example of a larger spreadsheet, I print out for each team their areas, so the print area is from column B to column H. I need a formula in B1 to give me the Team name when I filter the list, for example if I sort by Team C, I want "Team C" to be displayed in B1 or sort by Team D, "Team D" to be displayed. Any ideas?

oldchippy

So this is sort of a two part question... in the below snippet i have a row getting filled in with dates for each day for two years...that part seems to work fine. the next part of it will check the value it just entered, and if it equals a value from a different sheet, i want to select that column and apply a colored border to it. i think the method is .borderAround but not sure, i havent gotten that far... I'm getting a run time error 1004 'application defined or object defined error, here is the snippet


Code:

 
lastRow = Worksheets("Team Roster").Range("A65536").End(xlUp).Row
 
 
    For iColumn = 4 To 734          '730 days in two years, started with 4 because that was the first column to fill automatically
        Worksheets("Team Roster").Cells(5, iColumn).Value = Worksheets("Team Roster").Cells(5, iColumn - 1).Value + 1
 
        If Worksheets("Team Roster").Cells(5, iColumn).Value = Worksheets("Setup").Range("S8").Value Then
 
            Worksheets("Team Roster").Range(Cells(3, iColumn), Cells(lastRow, iColumn)).Select
 
        End If
    Next iColumn

Hi, I am hoping one of you guys will be able to help.

Bascially I have a Master Sheet that contains rows of data each with column headings

Column A is the Team Name
Column B is a Value
Column C is a Date

Throughout this sheet there can be mutiple instances of a Team Name (e.g. Team 1, Team 2 etc) with various values and dates

e.g

Team 1, 123, 19/09/10
Team 2, 345, 20/09/10
Team 3, 999, 20/09/10
Team 2, 456, 21/09/10
Team 1, 222, 20/09/10
etc etc

I then have separate tabs for each of these Teams

What i want to do is on each of these Team tabs, lookup each occurence of the team name on the Maste Sheet (e.g Team 1 tab will search for all occurences of "Team 1" in Column A on the Master Sheet) and return the Value in one column and the Date in another column.

I have used VLOOKUP but this only returns the first occurence of Team 1 it finds

Is there anything that will find ALL occurence of "Team 1" and list them on consecutive rows in the Team 1 tab?

I hope this makes sense

Any help will be very much appreciated

Thanks

Stu

Hi,
I'm new to all of this, and it can become a bit daunting at times, but what I'd like is some help in setting up a formula to enable the correct reference when multiple cells are used as references.

As you can see, I have 4 divisions in a local eight ball pool comp.
Within each division are 8 teams.
And then the table below it shows when team 1 for example, is to play team 8.
And then 2 weeks later, team 1 then plays team 7.
I want this formula to work with another excel sheet, so that when I put in the division, date, and home team number, the answer will appear in another cell.

Now we'll assume that cells A2:A9 Contain the team numbers,
Cell B2:B9 contains division 1 teams,
Cell C2:C9 division 2 teams and so on.

Any help with working this out would be much appreciated.

Division 1 Division 2 Division 3 Division 4 1 Team 1 Team 9 Team 17 Team 25 2 Team 2 Team 10 Team 18 Team 26 3 Team 3 Team 11 Team 19 Team 27 4 Team 4 Team 12 Team 20 Team 28 5 Team 5 Team 13 Team 21 Team 29 6 Team 6 Team 14 Team 22 Team 30 7 Team 7 Team 15 Team 23 Team 31 8 Team 8 Team 16 Team 24 Team 32 Round 1 Round 2 Round 3 15th Feb 22nd Feb 1st Mar 1 V 8 4 V 1 1 V 7 2 V 7 5 V 3 8 V 6 3 V 6 6 V 2 2 V 5 4 V 5 7 V 8 3 V 4

I have three columns, with "Home team", "Away team" and "Winner"

"Winner" can either be "H" (home), "A" (away) or "D" (draw)


So essentially my SS looks something like:


A / B / H
C / D / A
B / A / H
D / C / H
C / A / D
B / D / A

etc. as the list is continually updated.

I'm trying to calculate streaks.

For example, I would like to create a formula that calculates team A's win percentage over its past 10 games.

Using the SUM function and arrays, I have created a "stream" of results (i.e. 1,-1,1,-1,0,1,1,etc.) for team A in cells I21 to AD21, but this will continue to expand in the future.

Anyone smarter than me have any ideas?

COLUMNS A to F
H Team 30/12/07 $1.70 LOSS - no cnt > $1.60
H Team 30/12/07 $1.42 DRAW- 1st cnt-rules met
H Team 30/12/07 $1.47 LOSS - no cnt-1.47>1.42
H Team 01/01/08 $1.45 LOSS - 2nd cnt-rules met
A Team 02/01/08 $1.54 WON - no cnt-away game
H Team 02/01/08 $1.30 WON - no cnt < $1.40
H Team 03/01/08 $1.50 WON - "2" displayed
H Team 20/01/08 $1.41 DRAW - 1st cnt-rules met
H Team 20/01/08 $1.53 DRAW - no cnt-1.53>1.41
A Team 21/01/08 $1.59 LOSS - no cnt-away game
H Team 22/01/08 $1.53 LOSS - 2nd cnt-rules met
H Team 30/01/08 $1.45 DRAW - 3rd cnt-rules met
H Team 31/01/08 $1.42 LOSS - 4th cnt-rules met
H Team 01/02/08 $1.57 WON - "5" displayed
H Team 05/03/08 $1.45 WON - "1" displayed


What I'm trying to do is choose only one footy match for each unique date that meets my parameters to bet on, and to keep count of the amount of bets I place before I win!

The parameters are as follows;

* Have to be the lowest odds (ColD) but > than $1.40 and < $1.60.
* Be a home game (ColA).
* In the advent of same odds & same date, the team with the lowest alphabetical ranking should be counted.

As you can see in the last column the " " is the data I want displayed.
It shows that it took 2 legitimate bets before I posted my first win, hence "2" is displayed.
It takes another 5 legitimate bets before I get my next WIN, so "5" displayed.
The the very next bet I get another WIN straight up, so "1" is displayed.

Regards Peter

I'm trying to create an excel workbook that can create team rosters.

In worksheet 1, I have a list of all the players. IN subsequent worksheets, I have rosters built with each teams specific information.

On draft day, I want to put the team number that drafted the player in the column next to the name on worksheet 1 and have his name autopopulate on the respective roster worksheets.
I can use If and Vlookup but this leaves gaps between the players names in the worksheet

Any ideas?

Thanks

I have a situation where the first few columns in my sheet refer to a particular team of people, then the next few columns to a second team, etc.

My problem is that the teams can (and do) change frequently.
Thus one month team A might have 3 members & team B has 6 members while next month team A has 5 members and team B has 4 members.

I want to display the team name spread across the top of however many columns are in use this month...

I currently have my sheet set up such that I have the correct index number for the required team at the head of EVERY column, this in turn can be used to select the required name from a list. BUT this gives team name repeated in every column, and with the columns being narrow all I get is "TeaTeaTea" rather than "Team X"

How can I (automatically) have the "Team X" header displayed across the appropriate number of columns regardless of the varying number of members in the team?

If I have a series of data:

Col. A Col. B
Team 1
Team 2
Team 2
Team 1
Team 3
Team 5
Team 3
Team 4
...


In column A say I have 50 entries with 5 teams where there is alot of duplicates, is there a formula that I can put in B1 (assuming Team 1 is in cell A1) that will look in column A, and list only the values without the duplicates, looking like this:

Col. A Col. B
Team 1 Team 1
Team 2 Team 2
Team 2 Team 3
Team 1 Team 4
Team 3 Team 5
Team 5
Team 3
Team 4
...

Ok I have been trying to find some software that will help me with this, but the average price is $100 and I don't want to spend that kind of money if I don't know if it will work.

What I am trying to do is create a schedule for a 20 team fantasy baseball league.

We have 4 divisions of 5 with a 21 weekly match ups.

The divisions are as such:
Cyan: Team 1, Team 2, Team 3, Team 4, Team 5
Magenta: Team 6, Team 7, Team 8, Team 9, Team 10
Yellow: Team 11, Team 12, Team 13, Team 14, Team 15
Black: Team 16, Team 17, Team 18, Team 19, Team 20

Each team will play the teams in their division twice, and the rest of the schedule is filled in with non divisional teams that you won't play more than once.

I don't know if anyone can help with this, but every time I have had a problem that I couldn't figure out in Excel someone here has been able to help.

Let's say I have "teams" of individuals, where each individual is allowed to be on more than one team. Now I want to answer the question, "Name everybody that is on a team with Rick". How could I generate such a list? Below is an example:

RAW DATA:

Team Person_1 Person_2 Person_3
Team A Joe Chris
Team B Lee Fred
Team C Bob Henry Chris
Team D Rick Fred

DESIRED SUMMARY TABLE (all individuals associated with person on the far left, below)

Everyone on a team with Bob: Henry Chris
Everyone on a team with Chris: Joe Bob Henry
Everyone on a team with Fred: Lee Rick
Everyone on a team with Henry: Bob Chris
Everyone on a team with Joe: Chris
Everyone on a team with Lee: Fred
Everyone on a team with Rick: Fred

I am new to excel and need an experts help on a simple formula

I want to add the total of B21:B30 and if it is less than the value of C21:C30 then
Display "Team 2 Win" and vice versa.

Team 1 is the B Column
Team 2 is the C Column

This is what I got:
=SUMIF((B21:B30>C21:C30),"Team 1 Win","Team 2 Win")

Was I way off? BTW I am using Excel 2003.

thanks in advance!
Ben