i'm working on a spreadsheet and am trying to find code to copy just the value to a cell from another sheet in the same work book. i have 2 codes but i'm trying to find a way to do it in the background without the cells being selected.
the first code that does what i want is :
Sub Macro2()
'
' Macro2 Macro
'copies from inputs to safety
' copies update date
Sheets("Inputs").Select
Range("B4").Select
Selection.Copy
Sheets("Safety").Select
Range("e56").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'copies injury free days
Sheets("Inputs").Select
Range("E4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Safety").Select
Range("E58").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' copies last injury free date
Sheets("Inputs").Select
Range("E3").Select
Selection.Copy
Sheets("Safety").Select
Range("E57").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-69
Sheets("Inputs").Select
End Sub
the scroll is to get back to the top of the safety sheet.
the other code i have been trying is
Sub test3()
Dim wssafety As Worksheet, insheet As Worksheet
Set wssafety = Worksheets("Safety")
Set insheet = Worksheets("Inputs")
wssafety.Range("e56").FormulaR1C1 = "=Inputs!R[-52]C[-3]"
wssafety.Range("e57").FormulaR1C1 = "=Inputs!R[-54]C"
wssafety.Range("e58").FormulaR1C1 = "=Inputs!R[-54]C"
' trying to do it with out this part as it selects the cells and leaves them selected but it does change the formula to just values
' wssafety.Range("e56:e58").Select
' Selection.Copy
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
' :=False, Transpose:=False
'Application.CutCopyMode = False
'insheet.Select
End Sub
So what i would like it to do is in cell E56 on the Safety sheet copy the date ( and just the date, no formats etc) from B4 on the Inputs sheet. Both my codes use a formula to copy the cell e.g. (=Inputs!B4)I then have to copy and paste special over the cell to leave just the value and not the formula.
I prefer the way the second code puts in the formulas as no cells get selected and page does not auto scroll or open and close on that sheet. i'm attaching the work book.
I have tried using a value command but that was not accepted . I'm assuming my use of it was incorrect.
also in the sub attend:
Option Explicit
Sub attend()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''Use Select Case for each cell and its '''
'''character '''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim wsAtten As Worksheet
Dim oCell As Range
Set wsAtten = Worksheets("Attendance")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''Check each cell in the given range'''
For Each oCell In wsAtten.Range("B3:AF12")
' Presant p (black)
If StrConv(oCell.Value, 1) = "P" Then
oCell.FormulaR1C1 = "a"
'''Edit your font here'''
With oCell.Font
.Name = "Webdings"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -10477568
.TintAndShade = 4.99893185216834E-02
.ThemeFont = xlThemeFontNone
oCell.Font.Bold = True
End With
' late l L (Red)
ElseIf StrConv(oCell.Value, 1) = "L" Then
oCell.FormulaR1C1 = "L"
'''Edit your font here'''
With oCell.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16776961
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
oCell.Font.Bold = True
End With
' planned non attendance x (blue)
ElseIf StrConv(oCell.Value, 1) = "X" Then
oCell.FormulaR1C1 = "X"
'''Edit your font here'''
With oCell.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -4165632
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
oCell.Font.Bold = True
End With
' on leave o (blue)
ElseIf StrConv(oCell.Value, 1) = "O" Then
oCell.FormulaR1C1 = "O"
'''Edit your font here'''
With oCell.Font
.Name = "Arial"
.Size = 11
.Bold = True
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -4165632
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
' absent sick awol A (red)
ElseIf StrConv(oCell.Value, 1) = "A" Then
oCell.FormulaR1C1 = "A"
'''Edit your font here'''
With oCell.Font
.Name = "Arial"
.Size = 11
.Bold = True
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16776961
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
' day off D (black)
ElseIf StrConv(oCell.Value, 1) = "D" Then
oCell.FormulaR1C1 = "D/o"
With oCell.Font
.Name = "Arial"
.Bold = True
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -10477568
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With oCell.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Else
' StrConv(oCell.Value, 1) Is Empty Then
oCell.FormulaR1C1 = ""
With oCell.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 4.99893185216834E-02
.ThemeFont = xlThemeFontNone
.Bold = False
End With
With oCell.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next oCell
End Sub
is the only way i can stop it from changing the tick marks to A's when run again by changing the starting values? For example p will change the letter to an a and change the font to webdings. while A will change font to areal and make it red. but if you run it again it changes all the ticks to Big red A's.
Bookmarks