mykal66
03-08-2014, 04:17 AM
Hi again folks - 2 posts in one day but not real urgency on this as what i have works but i know it really dirty coding because i don't know how to use loops.
I created a absence tracker for work which in short summaries various absences form work (e.g. Sickness, Annual Leave, Training etc) onto a hidden worksheet for everyone.
The mess i use below allows a manager to select a user name then copy data for that user only onto another table and create a stacked column chart showing everything for that person.
I understand the concept of looping but never really figured out how to do it but this time i copied the same things so many times i finally got an 'project to large' message in vba (which i also didn't know about). As you can see the code is exactly the same apart from the name changes but i have a named list which i assume the loop would use and the copy values drop down 1 row for each person so essentially all i did was copy and paste, change the look up name then manually altered the 26 refs to each row.
As i said this is just a learning curve for me as this works but i would like to become a better amateur
Sub Button3_Click()
Dim Staff As String
Staff = Range("D2").Value
If Staff = ("Someone Smith") Then
Application.ScreenUpdating = False
Sheets("Summary").Select
Range("B4:C4").Select
Selection.Copy
Sheets("Admin").Select
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("E3:F3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("H3:I3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("K3:L3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
ActiveWindow.SmallScroll ToRight:=10
Range("N3:O3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("Q3:R3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("T3:U3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("W3:X3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
ActiveWindow.SmallScroll ToRight:=9
Range("Z3:AA3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AC3:AD3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AF3:AG3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AI3:AJ3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Staff = ("Someone Jones") Then
Application.ScreenUpdating = False
Sheets("Summary").Select
Range("B4:C4").Select
Selection.Copy
Sheets("Admin").Select
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("E4:F4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("H4:I4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("K4:L4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
ActiveWindow.SmallScroll ToRight:=10
Range("N4:O4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("Q4:R4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("T4:U4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("W4:X4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
ActiveWindow.SmallScroll ToRight:=9
Range("Z4:AA4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AC4:AD4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AF4:AG4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AI4:AJ4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Thanks again
I created a absence tracker for work which in short summaries various absences form work (e.g. Sickness, Annual Leave, Training etc) onto a hidden worksheet for everyone.
The mess i use below allows a manager to select a user name then copy data for that user only onto another table and create a stacked column chart showing everything for that person.
I understand the concept of looping but never really figured out how to do it but this time i copied the same things so many times i finally got an 'project to large' message in vba (which i also didn't know about). As you can see the code is exactly the same apart from the name changes but i have a named list which i assume the loop would use and the copy values drop down 1 row for each person so essentially all i did was copy and paste, change the look up name then manually altered the 26 refs to each row.
As i said this is just a learning curve for me as this works but i would like to become a better amateur
Sub Button3_Click()
Dim Staff As String
Staff = Range("D2").Value
If Staff = ("Someone Smith") Then
Application.ScreenUpdating = False
Sheets("Summary").Select
Range("B4:C4").Select
Selection.Copy
Sheets("Admin").Select
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("E3:F3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("H3:I3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("K3:L3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
ActiveWindow.SmallScroll ToRight:=10
Range("N3:O3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("Q3:R3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("T3:U3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("W3:X3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
ActiveWindow.SmallScroll ToRight:=9
Range("Z3:AA3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AC3:AD3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AF3:AG3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AI3:AJ3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Staff = ("Someone Jones") Then
Application.ScreenUpdating = False
Sheets("Summary").Select
Range("B4:C4").Select
Selection.Copy
Sheets("Admin").Select
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("E4:F4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("H4:I4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("K4:L4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
ActiveWindow.SmallScroll ToRight:=10
Range("N4:O4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("Q4:R4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("T4:U4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("W4:X4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
ActiveWindow.SmallScroll ToRight:=9
Range("Z4:AA4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AC4:AD4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AF4:AG4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Summary").Select
Range("AI4:AJ4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Admin").Select
Range("E16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Thanks again