VBA


Uncategorized

Updated Nov 6th, 2021

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.

Working?

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.

Final

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