BenGrobler
New Member
- Joined
- Apr 19, 2021
- Messages
- 31
- Office Version
- 2019
- 2016
- Platform
- Windows
I have the code written up but it does not work
is there anyone that can help with this,
it is repeated 65 times with numbers from 902 to 4121 inbetween.
When i enter data in the first sheet yard attendance then some of that data needs to be copied in the next sheet Trend next to the corresponding number.
so 902 is AWOP in cell C4 on sheet Yard Attendance (sheet 1) on the trend (sheet2) AWOP must appear on C4
Another examples is if 4120 is Sick is C4 on sheet 1 then sick must appear next to 4120 in Cell C64 in Sheet 2, if that makes sense?
I can have in sheet 1
A4 = 902 C4 = AWOP
A5 = 3643 C5 = Sick
A6 = 3813 C6 = Annual Leave
A7 = 4120 C7 = AWOP
Then on Sheet 2
C4 = C4 on sheet 1
C46 = C5 on Sheet 1
C40 = C6 on sheet 1
C64 = C7 on Sheet 1
Please help
is there anyone that can help with this,
it is repeated 65 times with numbers from 902 to 4121 inbetween.
When i enter data in the first sheet yard attendance then some of that data needs to be copied in the next sheet Trend next to the corresponding number.
so 902 is AWOP in cell C4 on sheet Yard Attendance (sheet 1) on the trend (sheet2) AWOP must appear on C4
Another examples is if 4120 is Sick is C4 on sheet 1 then sick must appear next to 4120 in Cell C64 in Sheet 2, if that makes sense?
I can have in sheet 1
A4 = 902 C4 = AWOP
A5 = 3643 C5 = Sick
A6 = 3813 C6 = Annual Leave
A7 = 4120 C7 = AWOP
Then on Sheet 2
C4 = C4 on sheet 1
C46 = C5 on Sheet 1
C40 = C6 on sheet 1
C64 = C7 on Sheet 1
Please help
VBA Code:
If Range("a4").Value = "902" Then
Range("C4").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("a4").Value = "903" Then
Range("C4").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("a4").Value = "910" Then
Range("C4").Select
Selection.Copy
Sheets("Trend").Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A5").Value = "902" Then
Range("C5").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A5").Value = "903" Then
Range("C5").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A6").Value = "902" Then
Range("C6").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A7").Value = "902" Then
Range("C7").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A8").Value = "902" Then
Range("C8").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A9").Value = "902" Then
Range("C9").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A10").Value = "902" Then
Range("C10").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A11").Value = "902" Then
Range("C11").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A12").Value = "902" Then
Range("C12").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A13").Value = "902" Then
Range("C13").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A14").Value = "902" Then
Range("C14").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A15").Value = "902" Then
Range("C15").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A16").Value = "902" Then
Range("C16").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A6").Value = "903" Then
Range("C6").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A7").Value = "903" Then
Range("C7").Select
Selection.Copy
Sheets("Trend").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A8").Value = "903" Then
Range("C8").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A9").Value = "903" Then
Range("C9").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A10").Value = "903" Then
Range("C10").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A11").Value = "903" Then
Range("C11").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A12").Value = "903" Then
Range("C12").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A13").Value = "903" Then
Range("C13").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A14").Value = "903" Then
Range("C14").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A15").Value = "903" Then
Range("C15").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
If Range("A16").Value = "903" Then
Range("C16").Select
Selection.Copy
Sheets("Trend").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Columns("C:C").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Calibri"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Columns("d:d").EntireColumn.AutoFit
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 10.86
Range("d1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Sheets("SAM ELF YARD ATTENDENCE").Select
Range("K13").Select
End Sub
Last edited by a moderator: