Cleaning Up My Code

[jjmalloy82]

Any thoughts or some direction on how I can clean this up? I know it's a bit all over the place right now.


Sub StatusCode()


Sheet22.Visible = xlSheetVisible




Sheet22.Select


Dim sshh As Worksheet


For Each sshh In Worksheets


If sshh.Name <> "UPDATE IN PROGRESS" Then sshh.Visible = xlSheetVeryHidden


Next




Application.ScreenUpdating = False






Dim wss As Worksheet


For Each wss In ActiveWorkbook.Worksheets


wss.Visible = xlSheetVisible


Next wss


'this copies previous months/reports data for a recon to changes






Sheet8.Select

Range("Table1").Select
Selection.Copy
Sheets("ConsolidatedChange").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False




'Step 1 is to get the common status listed on each tab


Sheet1.Select


Range("R1").Value = "Status"
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & LRow).Value = "Forecast"


Sheet2.Select


Range("R1").Value = "Status"
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & LRow).Value = "Cancelled"


Sheet3.Select


Range("R1").Value = "Status"
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & LRow).Value = "Confirmed"



Sheet4.Select


Range("R1").Value = "Status"
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & LRow).Value = "Delivered"



Sheet5.Select


Range("R1").Value = "Status"
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & LRow).Value = "Need Task Created"




Sheet6.Select


Range("R1").Value = "Status"
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & LRow).Value = "Proposed"


Sheet7.Select


Range("R1").Value = "Status"
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & LRow).Value = "Provisional"






Sheet31.Select
Range("R1").Value = "Status"
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & LRow).Value = "In Planning"


Sheet12.Select


LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("AW1").Value = "UID"
Range("AX1").Value = "Travel Help"
Range("AY1").Value = "Control"


Range("aw2:aw" & LRow).Formula = "=F2&"" - ""&G2"
Range("aw2:aw" & LRow).Value = Range("aw2:aw" & LRow).Value


Range("ax2:ax" & LRow).Formula = "=O2"
Range("ax2:ax" & LRow).Value = Range("ax2:ax" & LRow).Value


Range("ay2:ay" & LRow).Formula = "=IF(I2="""","""",I2)"
Range("ay2:ay" & LRow).Value = Range("ay2:ay" & LRow).Value




Sheet30.Select
LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("t1").Value = "UID Job Type"
Range("u1").Value = "Job Type"




'UID Job Type


Range("t2:t" & LRow).Formula = "=A2&"" - ""&P2"
Range("t2:t" & LRow).Value = Range("t2:t" & LRow).Value




'Job Type


Range("u2:u" & LRow).Formula = "=J2"
Range("u2:u" & LRow).Value = Range("u2:u" & LRow).Value




'This Clears the current data


Sheet8.Select


ActiveSheet.ListObjects("Table1").Delete

Sheet10.Select
Range("AG1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Sheets("Consolidated").Select


Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'next set of coding handles the Consolidated File


Dim rr As Range
Set rr = Worksheets("Forecast").Range("A2:R" & Worksheets("Forecast").Range("A" & Rows.Count).End(xlUp).Row)
Set rr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rr.Copy
rr2.PasteSpecial xlValues


Dim rrr As Range
Set rrr = Worksheets("Cancelled").Range("A2:R" & Worksheets("Cancelled").Range("A" & Rows.Count).End(xlUp).Row)
Set rrr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rrr.Copy
rrr2.PasteSpecial xlValues


Dim rrrr As Range
Set rrrr = Worksheets("Confirmed").Range("A2:R" & Worksheets("Confirmed").Range("A" & Rows.Count).End(xlUp).Row)
Set rrrr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rrrr.Copy
rrrr2.PasteSpecial xlValues

Dim rrrrr As Range
Set rrrrr = Worksheets("Delivered").Range("A2:R" & Worksheets("Delivered").Range("A" & Rows.Count).End(xlUp).Row)
Set rrrrr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rrrrr.Copy
rrrrr2.PasteSpecial xlValues

Dim rrrrrr As Range
Set rrrrrr = Worksheets("To be Planned").Range("A2:R" & Worksheets("To be Planned").Range("A" & Rows.Count).End(xlUp).Row)
Set rrrrrr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rrrrrr.Copy
rrrrrr2.PasteSpecial xlValues

Dim rrrrrrr As Range
Set rrrrrrr = Worksheets("Proposed").Range("A2:R" & Worksheets("Proposed").Range("A" & Rows.Count).End(xlUp).Row)
Set rrrrrrr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rrrrrrr.Copy
rrrrrrr2.PasteSpecial xlValues


Dim rrrrrrrr As Range
Set rrrrrrrr = Worksheets("Provisional").Range("A2:R" & Worksheets("Provisional").Range("A" & Rows.Count).End(xlUp).Row)
Set rrrrrrrr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rrrrrrrr.Copy
rrrrrrrr2.PasteSpecial xlValues


Dim rrrrrrrrr As Range
Set rrrrrrrrr = Worksheets("CTS-Regular").Range("A2:R" & Worksheets("CTS-Regular").Range("A" & Rows.Count).End(xlUp).Row)
Set rrrrrrrrr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rrrrrrrrr.Copy
rrrrrrrrr2.PasteSpecial xlValues

Dim rrrrrrrrrr As Range
Set rrrrrrrrrr = Worksheets("CTS-NST").Range("A2:R" & Worksheets("CTS-NST").Range("A" & Rows.Count).End(xlUp).Row)
Set rrrrrrrrrr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rrrrrrrrrr.Copy
rrrrrrrrrr2.PasteSpecial xlValues

Dim rrrrrrrrrrr As Range
Set rrrrrrrrrrr = Worksheets("BudgetClean").Range("A2:R" & Worksheets("BudgetClean").Range("A" & Rows.Count).End(xlUp).Row)
Set rrrrrrrrrrr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rrrrrrrrrrr.Copy
rrrrrrrrrrr2.PasteSpecial xlValues


Dim rrrrrrrrrrrrr As Range
Set rrrrrrrrrrrrr = Worksheets("In Planning").Range("A2:R" & Worksheets("In Planning").Range("A" & Rows.Count).End(xlUp).Row)
Set rrrrrrrrrrrrr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rrrrrrrrrrrrr.Copy
rrrrrrrrrrrrr2.PasteSpecial xlValues


Columns("J:K").Select
Selection.NumberFormat = "[$-409]d-mmm-yy;@"

Columns("AF:AF").Select
Selection.NumberFormat = "0"

'Next is the FormulaEntry




Sheet12.Select


Range("AW1").Value = "UID"
Range("AX1").Value = "Travel Help"


LRow = Range("A" & Rows.Count).End(xlUp).Row


'UID


Range("aw2:aw" & LRow).Formula = "=F2&"" - ""&G2"
Range("aw2:aw" & LRow).Value = Range("aw2:aw" & LRow).Value




'Travel Help


Range("ax2:ax" & LRow).Formula = "=O2"
Range("ax2:ax" & LRow).Value = Range("ax2:ax" & LRow).Value




'Status of Jobs








Sheet8.Select


LRow = Range("A" & Rows.Count).End(xlUp).Row


'Office Budget


Range("s2:s" & LRow).Formula = "=VLOOKUP(B2,Validation!B:C,2,FALSE)"
Range("s2:s" & LRow).Value = Range("s2:s" & LRow).Value


'Area


Range("t2:t" & LRow).Formula = "=VLOOKUP(B2,Validation!B:D,3,FALSE)"
Range("t2:t" & LRow).Value = Range("t2:t" & LRow).Value


'Month to Deliver


Range("u2:u" & LRow).Formula = "=DATE(YEAR(K2),MONTH(K2),1)"
Range("u2:u" & LRow).Value = Range("u2:u" & LRow).Value


'FY


Range("v2:v" & LRow).Formula = "=VLOOKUP(U2,Validation!E:F,2,FALSE)"
Range("v2:v" & LRow).Value = Range("v2:v" & LRow).Value


'Product Vertical


Range("w2:w" & LRow).Formula = "=IF(M2="""",""No Product Listed"",VLOOKUP(M2,Validation!R:U,4,FALSE))"
Range("w2:w" & LRow).Value = Range("w2:w" & LRow).Value


'Complexity


Range("x2:x" & LRow).Formula = "=VLOOKUP(W2,Validation!X:Y,2,FALSE)"
Range("x2:x" & LRow).Value = Range("x2:x" & LRow).Value


'Planner


Range("y2:y" & LRow).Formula = "=IF(ISERROR(VLOOKUP(Consolidated!G2,'Contract Owners'!G:M,6,FALSE)),""Need Planner Listed"",VLOOKUP(Consolidated!G2,'Contract Owners'!G:M,6,FALSE))"
Range("y2:y" & LRow).Value = Range("y2:y" & LRow).Value


'Client Executive


Range("z2:z" & LRow).Formula = "=IF(ISERROR(VLOOKUP(Consolidated!G2,'Contract Owners'!G:M,7,FALSE)),""Need CE Listed"",VLOOKUP(Consolidated!G2,'Contract Owners'!G:M,7,FALSE))"
Range("z2:z" & LRow).Value = Range("z2:z" & LRow).Value




'UID (Used for Finance Look Up)


Range("aa2:aa" & LRow).Formula = "=G2&"" - ""&O2"
Range("aa2:aa" & LRow).Value = Range("aa2:aa" & LRow).Value




'Travel Days


Range("ab2:ab" & LRow).Formula = "=IF(ISERROR(VLOOKUP(AA2,'Finance Report'!AW:AX,2,FALSE)),0,VLOOKUP(AA2,'Finance Report'!AW:AX,2,FALSE))"
Range("ab2:ab" & LRow).Value = Range("ab2:ab" & LRow).Value




'Total Days


Range("ac2:ac" & LRow).Formula = "=SUM(AB2+Q2)"
Range("ac2:ac" & LRow).Value = Range("ac2:ac" & LRow).Value


'Tier
Range("ad2:ad" & LRow).Formula = "=IF(L2=""Tier 2"",""Tier 2"",IF(L2=""IG - Out"",""IG - Out"",IF(L2=""IG - In"",""IG - In"",IF(L2=""CTS"",""CTS"",IF(ISERROR(VLOOKUP(M2,Validation!G:G,1,FALSE)),""Tier 1"",""Tier 2"")))))"
Range("ad2:ad" & LRow).Value = Range("ad2:ad" & LRow).Value




'Core / NST


Range("ae2:ae" & LRow).Formula = "=IF(M2=""Core"",""Core"",IF(M2=""NST"",""NST"",IF(ISNUMBER(SEARCH(""Transi"",L2)),""NST"",""Core"")))"
Range("ae2:ae" & LRow).Value = Range("ae2:ae" & LRow).Value




'Assessor Territory Name


Range("af2:af" & LRow).Formula = "=IF(N2="""",VLOOKUP(B2,Validation!AC:AD,2,FALSE),IF(ISERROR(VLOOKUP(Consolidated!N2,AmericasEmpList!A:B,2,FALSE)),""IG - In"",VLOOKUP(Consolidated!N2,AmericasEmpList!A:B,2,FALSE)))"
Range("af2:af" & LRow).Value = Range("af2:af" & LRow).Value






Dim rrrrrrrrrrrr As Range
Set rrrrrrrrrrrr = Worksheets("Intragroup Out").Range("A2:AG" & Worksheets("Intragroup Out").Range("A" & Rows.Count).End(xlUp).Row)
Set rrrrrrrrrrrr2 = Worksheets("Consolidated").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
rrrrrrrrrrrr.Copy
rrrrrrrrrrrr2.PasteSpecial xlValues


'Office OU


Range("ag2:ag" & LRow).Formula = "=IF(AF2=""IG - In"",B2,IF(ISERROR(VLOOKUP(AF2,Validation!AE:AF,2,FALSE)),"""",VLOOKUP(AF2,Validation!AE:AF,2,FALSE)))"
Range("ag2:ag" & LRow).Value = Range("ag2:ag" & LRow).Value




'UID Job Type


Range("al1").Value = "UID Job Type"
Range("al2:al" & LRow).Formula = "=A2&"" - ""&G2"
Range("al2:al" & LRow).Value = Range("al2:al" & LRow).Value




'Job Type


Range("am1").Value = "Job Type"
Range("am2:am" & LRow).Formula = "=IF(ISERROR(VLOOKUP(AL2,Sheet4!T:U,2,FALSE)),L2,VLOOKUP(AL2,Sheet4!T:U,2,FALSE))"
Range("am2:am" & LRow).Value = Range("am2:am" & LRow).Value




'Has Control Number?
Range("aj1").Value = "Has Control Number?"
Range("aj2:aj" & LRow).Formula = "=IF(ISERROR(VLOOKUP(AA2,'Finance Report'!AW:AY,3,FALSE)),""No"",VLOOKUP(AA2,'Finance Report'!AW:AY,3,FALSE))"
Range("aj2:aj" & LRow).Value = Range("aj2:aj" & LRow).Value




'Business Cat


Range("ah2:ah" & LRow).Formula = "=IF(B2="""","""",IF(ISERROR(VLOOKUP(AM2,Validation!BP:BQ,2,FALSE)),""Check"",VLOOKUP(AM2,Validation!BP:BQ,2,FALSE)))"
Range("ah2:ah" & LRow).Value = Range("ah2:ah" & LRow).Value


'Business Type
Range("ai1").Value = "Business Type"
Range("ai2:ai" & LRow).Formula = "=IF(B2="""","""",VLOOKUP(AH2,Validation!BV:BW,2,FALSE))"
Range("ai2:ai" & LRow).Value = Range("ai2:ai" & LRow).Value


'New / Renewal / Existing / Other
Range("ak1").Value = "New / Renewal / Existing / Other"
Range("ak2:ak" & LRow).Formula = "=IF(ISERROR(VLOOKUP(AI2,Validation!BY:BZ,2,FALSE)),R2&"" - ""&AI2,VLOOKUP(AI2,Validation!BY:BZ,2,FALSE))"
Range("ak2:ak" & LRow).Value = Range("ak2:ak" & LRow).Value






'convert back to a table
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AM$200000"), , xlYes).Name _
= "Table1"


Sheet8.Select
Range("Table1").Select
Selection.Copy
Sheets("ConsolidatedForSnap").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False





Sheet23.Select




ActiveWorkbook.RefreshAll






Dim sh As Worksheet


For Each sh In Worksheets


If sh.Name <> "SnapShot2Change" And sh.Name <> "SnapShot2" And sh.Name <> "Consolidated" And sh.Name <> "Home" And sh.Name <> "SnapShot" Then sh.Visible = xlSheetVeryHidden


Next


MsgBox "Finished Update"








End Sub

 

[mrhstn]

You don't need to select or activate a sheet to act upon it...

So where you have

Sheet5.Select


Range("R1").Value = "Status"
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & lRow).Value = "Need Task Created"

You can more efficiently use

With Sheet5
lRow = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("R1").Value = "Status"
    .Range("R2:R" & lRow).Value = "Need Task Created"
End With

And then you have quite a lot of recycled code, like...

'New / Renewal / Existing / Other
Range("ak1").Value = "New / Renewal / Existing / Other"
Range("ak2:ak" & LRow).Formula = "=IF(ISERROR(VLOOKUP(AI2,Validation!BY:BZ,2,FALSE)),R2&"" - ""&AI2,VLOOKUP(AI2,Validation!BY:BZ,2,FALSE))"
Range("ak2:ak" & LRow).Value = Range("ak2:ak" & LRow).Value

Which could be...

'New / Renewal / Existing / Other
Range("ak1").Value = "New / Renewal / Existing / Other"
With Range("AK2:AK" & lRow)
    .Formula = "=IF(ISERROR(VLOOKUP(AI2,Validation!BY:BZ,2,FALSE)),R2&"" - ""&AI2,VLOOKUP(AI2,Validation!BY:BZ,2,FALSE))"
    .Value = .Value
End With

There is probably other things you can do but this will get you started then once you've tidied it up a little come back and I'm sure we'll take another look...

 

[jjmalloy82]

thanks - will work on this and update on success later

 

[mrhstn]

Another observation, you are doing a very similar action across 'step 1'

This section here...

'Step 1 is to get the common status listed on each tab




Sheet1.Select
Range("R1").Value = "Status"
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & lRow).Value = "Forecast"


Sheet2.Select
Range("R1").Value = "Status"
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & lRow).Value = "Cancelled"


Sheet3.Select
Range("R1").Value = "Status"
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & lRow).Value = "Confirmed"


Sheet4.Select
Range("R1").Value = "Status"
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & lRow).Value = "Delivered"


Sheet5.Select
Range("R1").Value = "Status"
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & lRow).Value = "Need Task Created"


Sheet6.Select
Range("R1").Value = "Status"
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & lRow).Value = "Proposed"


Sheet7.Select
Range("R1").Value = "Status"
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & lRow).Value = "Provisional"


Sheet31.Select
Range("R1").Value = "Status"
lRow = Range("A" & Rows.Count).End(xlUp).Row
Range("R2:R" & lRow).Value = "In Planning"

could be rewritten like this...

Dim ws As Worksheet
Dim wsString As String
Dim rwVal(1 To 8) As String
Dim counter As Integer
counter = 1
wsString = "|Sheet1|Sheet2|Sheet3|Sheet4|Sheet5|Sheet6|Sheet7|Sheet31|"
rwVal = Array("Forecast", "Cancelled", "Confirmed", "Delivered", "Need Task Created", "Proposed", "Provisional", "In Planning")
For Each ws In ActiveWorkbook.Worksheets
    If InStr(1, wsString, "|" & ws.CodeName & "|") Then
    With ws
    lRow = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("R1").Value = "Status"
        .Range("R2:R" & lRow).Value = rwVal(counter)
    End With
    counter = counter + 1
    End If
Next ws

I don't have the whole workbook so you must test this on a copy of your workbook to see that a) it does exactly as expected, b) runs quicker