Changing Paste Special to Values and Format

**rhudgins** · 03-18-2010, 04:18 PM

In the code below I would like to change the statement to paste as values and format. When the macro runs now it pastes as values but not the format. Can anyone help?

Private Sub RunIt()

If Code > 0 Then
sheet1.Range("I14", "IV18").Select
Selection.Copy
Worksheets("Results").Activate
ActiveSheet.Range("I" & Code * 5 + 9).Select
End If

ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False

**Leith Ross** · 03-18-2010, 05:11 PM

Hello rhudgins,

Here is the revised code. The PasteSpecial has to be done twice on the range, once for values and again for formats.

Private Sub RunIt()

  If Code > 0 Then
    Sheet1.Range("I14", "IV18").Copy
    With Worksheets("Results").Range("I" & Code * 5 + 9)
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpceial Paste:=xlPasteFormats
    End With
  End If

**rhudgins** · 03-18-2010, 06:25 PM

Thanks! In addition in the attached excel sheet I would like to add a code that copies the values on the data tab from column C and D to the sheet1 tab in cells F7 and F9. Can you help me with this?

Private Sub getreport(Code As Integer)
'Copy dates
    StartDate = ActiveCell.Offset(0, 8).Value
    EndDate = ActiveCell.Offset(0, 9).Value
'First line
    CellToCopy = "G" & ActiveCell.Row
    Range(CellToCopy).Select
    Selection.Copy
    If (ActiveCell.Offset(0, -5).Value = "Long") Then
        sheet1.Select
        sheet1.Range("B7").Select
        sheet1.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False
    Else
        sheet1.Select
        sheet1.Range("D7").Select
        sheet1.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False
    End If
    Worksheets("Data").Select
    Range("A" & ActiveCell.Row).Select
    BasketCode = ActiveCell.Offset(1, 1).Value
    StartRow = ActiveCell.Row
    OffsetRow = 0
    Do Until ActiveCell.Offset(OffsetRow, 0).Value <> Code
        OffsetRow = OffsetRow + 1
    Loop
    Range("G" & StartRow + 1, "G" & StartRow + OffsetRow - 1).Select
    Selection.Copy
    If (BasketCode = "Short Basket") Then
        sheet1.Select
        sheet1.Range("D13").Select
        sheet1.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False
    Else
        sheet1.Select
        sheet1.Range("B13").Select
        sheet1.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False
    End If
    CurrentDate = Now()
    If (EndDate > CurrentDate) Then
        EndDate = ""
    End If

    sheet1.Range("C36").Value = StartDate
    sheet1.Range("C37").Value = EndDate
    
    CurRow = StartRow + OffsetRow
    
    
    Application.Run "RefreshEntireWorksheet"
    Application.OnTime Now + TimeValue("00:00:10"), "RunIt"
    
    
End Sub