Please help clean up my convoluted VBA code. It seems very redundant and repetitive.

[CapRavOr]

This is some (poor) vba code I wrote in order to take multirow information and unpivot it for a table that will be used elsewhere. I know unpivoting can be done in power pivot or even just in excel, but in my situation, having a macro run this code would be a whole hell of a lot easier. Thank you for any help!

Sub macTargetStageMacro()
'
' Takes the values, B5:M11, and transposes them onto the Target Staging table
' Then, takes X5:Y88, copies, and pastes to P5
' Then, takes the Market in B2, copies, and pastes to the entire Market column in the stage table

' First, establishes the proper worksheet, Target Staging

    Sheets("Target Staging").Select
    
' Next, takes the information held in the data table and paste/transposes it to S+12

    Range("B5:M5").Select
    Selection.Copy
    Range("tblTargetStaging[Value]").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    
' Selects the Program/Modality from column A, copies, and pastes it in the "Program Modality" column, next to the data that was just pasted
    
    Range("A5").Select
    Selection.Copy
    Range("R5:R16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("B6:M6").Select
    Selection.Copy
    Range("S17").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A6").Select
    Selection.Copy
    Range("R17:R28").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("B7:M7").Select
    Selection.Copy
    Range("S29").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A7").Select
    Selection.Copy
    Range("R29:R40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("B8:M8").Select
    Selection.Copy
    Range("S41").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A8").Select
    Selection.Copy
    Range("R41:R52").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("B9:M9").Select
    Selection.Copy
    Range("S53").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A9").Select
    Selection.Copy
    Range("R53:R64").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("B10:M10").Select
    Selection.Copy
    Range("S65").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A10").Select
    Selection.Copy
    Range("R65:R76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("B11:M11").Select
    Selection.Copy
    Range("S77").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A11").Select
    Selection.Copy
    Range("R77:R88").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

' Next, takes the Periods and Dates to the right of the table, copies, and pastes them into the Target Stage table
    
    Range("X5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("P5").Select
    ActiveSheet.Paste
    
' Finally, takes the Market from B2 and pastes it entirely through column O
    
    Range("B2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("O5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    
' I might need to test this out
    
'    ContinueToAppendToTargetTable = MsgBox("Continue Appending the Target Table?", 1, "Continue to Append")
'        If ContinueToAppendToTargetTable = vbOK Then

' Append to the Target table
    
    Sheets("Target Staging").Select
    Range("tblTargetStaging").Copy
    Sheets("Target (Budget) Data").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
    Sheets("Target Staging").Select
    Application.CutCopyMode = False
    
'        ElseIf ContinueToAppendToTargetTable = vbCancel Then
'    End If

'Clear the staging table

    Range("O5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    ActiveSheet.ListObjects("tblTargetStaging").Resize Range("$O$4:$S$5")
    Range("B5:M11").Select
    Selection.ClearContents
    Range("A1").Select
    Application.CutCopyMode = False
    
End Sub

 

[myall_blues]

As an almost universal rule, it it not necessary to 'select' anything prior to operating on it.

Thus for example your first few lines can be reduced to:

' Take information in Target Staging data table and transpose to S+12

    Sheets("Target Staging").Range("B5:M5").Copy
    Range("tblTargetStaging[Value]").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True