Add a New Sheet and named “ALL”
Sheets.Add.Name = "ALL"
Go to the DST tab, select all rows, not including the top two, and copy
Sheets("DST").Select
Rows("2:29").Select
Selection.Copy
Go to the “All” tab and insert
Sheets("ALL").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Do the same for “K, TE, WR, RB, QB” tabs.
What is the Application.CutCopyMode” line that was added when recording the macro? This line of code “clears” the clipboard*. If you’ve copied an Excel cell, running this line of code will remove the animation around the copied cell. CutCopyMode is an application-level property that indicates whether a Microsoft Office program (ex. Excel) is in Cut or Copy Mode. While in copy (or cut) mode, Excel has something stored in it’s clipboard which can be pasted.
Can it be deleted from the macro without effecting result? Yes, although I am going to leave the last one anyway.
Sheets("K").Select
Rows("2:31").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ALL").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
How to make the number of rows selected dynamic?
This sort of worked, leaves blank rows after the Kickers though.
Dim PositionTabLastRow As Long
PositionTabLastRow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("DST").Select
Range("a2:L" & PositionTabLastRow).Select
Selection.Copy
Sheets("ALL").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Sheets("K").Select
Range("a2:L" & PositionTabLastRow).Select
Selection.Copy
Sheets("ALL").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Note: All Tabs besides the Kicker and DST have a row with an A with an accent on top so the first row is the third.
Referring to a Range
Can refer to a range with Range(“a1:l280”) or Cell(1, 1)
Cells(Row Number, Column Number)
I keep seeing “Dim whatEvs as Long” in the code. What is Long?
A long integer is a number that can be used for a variable involving greater numbers than integers. To declare a variable that would hold such a large number, use the Long data type.
I was close but I noticed the macro was not selecting all the players, just 27, meaning the row variable is static. It was grabbing 28 for the K and DST though because of the different start ranges for those tabs. So it was obviosu the variable was specific to one tab, in this case the DST Tab. So I needed a different “LastRow” variable for each tab. Pretty easy to implement.
Sub CreateAllPlayersTab()
'
' CreateAllPlayersTab Macro
'
Dim DST_TabLastRow As Long
Dim K_TabLastRow As Long
Dim TE_TabLastRow As Long
Dim WR_TabLastRow As Long
Dim RB_TabLastRow As Long
Dim QB_TabLastRow As Long
DST_TabLastRow = Worksheets("DST").Range("A" & Rows.Count).End(xlUp).Row
K_TabLastRow = Worksheets("K").Range("A" & Rows.Count).End(xlUp).Row
TE_TabLastRow = Worksheets("TE").Range("A" & Rows.Count).End(xlUp).Row
WR_TabLastRow = Worksheets("WR").Range("A" & Rows.Count).End(xlUp).Row
RB_TabLastRow = Worksheets("RB").Range("A" & Rows.Count).End(xlUp).Row
QB_TabLastRow = Worksheets("QB").Range("A" & Rows.Count).End(xlUp).Row
'
Sheets.Add.Name = "ALL"
Sheets("DST").Select
Range("a2:L" & DST_TabLastRow).Select
Selection.Copy
Sheets("ALL").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Sheets("K").Select
Range("a2:L" & K_TabLastRow).Select
Selection.Copy
Sheets("ALL").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Sheets("TE").Select
Range("a3:L" & TE_TabLastRow).Select
Selection.Copy
Sheets("ALL").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Sheets("WR").Select
Range("a3:L" & WR_TabLastRow).Select
Selection.Copy
Sheets("ALL").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Sheets("RB").Select
Range("a3:L" & RB_TabLastRow).Select
Selection.Copy
Sheets("ALL").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Sheets("QB").Select
Range("a3:L" & QB_TabLastRow).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ALL").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
End Sub