Copy row from 1 worksheet to multiple other worksheets

[HRSPWR]

Please allow me to apologize in advance for my lack of knowledge and I would greatly appreciate your help. To be brief, I have a workbook which contains a few worksheets. One of which is a list of NFL teams (Team List) in column A. Each team has additional columns with data in columns B-F. This data changes weekly.

Then, each team has a corresponding worksheet with the team name. These team worksheets need to show data from the Team List worksheet in C2:G2. On these same team worksheets the weekly schedule is shown and each of the teams played has data listed from the Team List worksheet.

I am manually updating the "Team List" (an issue to be tackled later hopefully) and I would like each teams' data to automatically update whenever the Team List updates. Keep in mind, that each team listed on the Team List worksheet will be listed in multiple locations on each team's worksheet because these teams will play each other. This probably only makes sense to me lol, so if I can clarify please let me know.

 

[ravishankar]

try the following macro codes

Sub jjj()
Dim a As Integer, x As Integer
Dim b As String
x = Sheets("summary").Cells(Rows.Count, 1).End(xlUp).Row
    For a = 2 To x
    b = Sheets("summary").Cells(a, 1)
    Sheets("summary").Range("B" & a & ":F" & a).Copy
    Sheets(b).Range("B2").PasteSpecial
    Next a
MsgBox "complete"
End Sub

change the sheet name from summary to wherever list of teams and data are stored. this will get you started
ravi shankar

 

[HRSPWR]

Sorry for the delay in my response - very busy at work. This works perfectly Ravi, thank you!

On each team's individual page, I am also listing that team's schedule which is made up of various teams from the "Team List" sheet. Each team will play a minimum of 16 games. So is there a way to automate updating that team's opponent as the "Team List" data is updated?

 

[ravishankar]

it is possible. I have to know the layout of your team list page and opponents page.
ravishankar

 

[HRSPWR]

Here's a Dropbox link to the workbook. https://www.dropbox.com/s/n548k1sykq490pu/2018 NFL Workbook.xlsm?dl=0

Thanks for your help!

 

[HRSPWR]

I didn't do a very good job explaining what I am needing help with, so here goes again. I have a workbook (see previous dropbox link if you want to see it) that has many sheets. The sheets I am concerned with are the Team List sheet and the individual Team Pages sheets. I update the Team List page weekly - replacing what is there with new values. I have a macro that then places these new values on that Team's individual page in a fixed location (C2:G2).

What I am needing is a mechanism to place the Team's new value in a dynamic spot as that team plays the other teams on the Team List sheet. Like this, DAL has a certain value (kind of a strength value), then DAL plays CAR, NYG and SEA in 3 consecutive weeks. DAL will continue playing weekly games for a total of 16 weeks. I need the strength value assigned to CAR, NYG and SEA (as well as the other teams they will play) to populate on DAL's page as the weeks progress. In this example, CAR strength would need to show up at C3:G3, NYG at C4:G4 and SEA at C5:G5, on DAL's page. Other teams will also play CAR, NYG and SEA and so the location of the information will not always be the same as on DAL's page.

Any help is greatly appreciated, thank you.

 

[HRSPWR]

I am trying to customize a Macro I found that may get me a little closer to a solution. Part of this Macro needs to look in my sheets to see if some matching criteria exists. My sheets all have team names - not numbered. So is there a way to search a Range of sheets like you would a range of cells? Like for cells you may use B2:G16 for example. Will this concept work for my sheets (I have 32)? Like Sheets("ATL:WSH"). I know it won't work as I have written here, but maybe there is a modification to accomplish this?

Any help is greatly appreciated! Bill

 

[HRSPWR]

Hey, hello and thank you to all that have viewed and pondered an attempt of assisting in solving this and to those that actually did attempt a solution. For those that have a similar issue (and apparently I was not alone, lol) I wanted to post the solution offered by a friend (spoiler...it is a formula) that may not be as elegant as a well thought out Macro, but gets me where I need to be for now.

=INDEX(Table1[#All],MATCH($A3,Table1[[#All],[TEAM]],0),MATCH(B$1,Table1[#Headers],0))

. Yes, it is a formula and not a Macro so my bad in asking for help with a Macro...TY again!

 

[ravishankar]

run the macro hrspwr in the excel file at https://drive.google.com/open?id=1-tlDeB7s8MmsyTYGJiwfzcj7Cu3EAshM
hope it meets atleast a part of what you want. the codes it has is

Sub hrspwr()
Dim x As Long, a As Long, y As Long, z As Long
Dim b As String, e As String, f As String
Dim d As Long, g As Long, h As Long, j As Long
x = Sheets("Team List").Cells(Rows.Count, 1).End(xlUp).Row
    For a = 2 To x
    b = Sheets("Team List").Cells(a, 1)
    Sheets.Add.Name = b
       Sheets("Team List").Range("B" & a & ":F" & a).Copy
    Sheets(b).Range("C2").PasteSpecial
    Sheets(b).Range("B2") = "hrspwr"
    Sheets(b).Range("B2").Font.ColorIndex = 2
    Next a
    
    y = Sheets("WEEKLY MATCH").Cells(Rows.Count, 2).End(xlUp).Row
    z = Sheets("WEEKLY MATCH").Cells(Rows.Count, 3).End(xlUp).Row
    For d = 2 To y
    e = Sheets("WEEKLY MATCH").Cells(d, 2) 'opponent
    f = Sheets("WEEKLY MATCH").Cells(d, 3) 'home
    g = WorksheetFunction.Match(e, Sheets("team list").Range("A:A"), 0)
    h = Sheets(e).Cells(Rows.Count, 2).End(xlUp).Row
    j = Sheets(f).Cells(Rows.Count, 2).End(xlUp).Row ' home row
    Sheets(f).Range("B" & j + 1) = e
    Sheets("Team List").Range("B" & g & ":F" & g).Copy
    Sheets(f).Range("C" & j + 1).PasteSpecial
    Next d
MsgBox "complete"
End Sub

ravishankar