VBA Help

BenGrobler

New Member
Joined
Apr 19, 2021
Messages
31
Office Version
  1. 2019
  2. 2016
Platform
  1. 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

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:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top