Results 1 to 3 of 3

how to copy just values.

Threaded View

  1. #1
    Registered User
    Join Date
    05-27-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    12

    how to copy just values.

    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.
    Attached Files Attached Files
    Last edited by vash1628; 05-29-2012 at 04:53 PM. Reason: forgot to add workbook

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1