title: Copy values and format in VBA macro excel
body: <p>I am quite new to this stuff i.e. writing macros in excel. I need to modify a macro where content is copied from 1 sheet to another but here only values are being copied whereas I would like to copy cell formatting as well. I have searched on a number of forums and tried to copy their formats but still no luck. May be I missing out something in my code.  </p>
  
  <p>strActiveSheet - Sheet from which content is copied</p>
  
  <p>res - Sheet on which content is being pasted</p>
  
  <pre><code>With Sheets(strActiveSheet)
      .Select
      .Range(.Cells(DataStartRow - 1, 1), .Cells(DataStartRow - 1, 4)).Select
      Selection.Copy
      End With
  
  'Paste
   Sheets(res).Select
   Cells(9, 1).Select
   Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  </code></pre>
  
  <p>This code just copies the value. I have tried to use the following code but to no avail:</p>
  
  <pre><code>Sheets(res).Select
       Cells(9, 1).Select
       Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  </code></pre>
  
  <p>If I use the following code, this will just copy the formatting but omit the values:</p>
  
  <pre><code>Sheets(res).Select
           Cells(9, 1).Select
           Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  </code></pre>
  
  <p>May be I think I am not writing PasteSpecial code in correct format.</p>
  
  <p>Can someone please help me to get the correct format for copying and pasting values as well as cell format from one sheet to another in VBA ?</p>
  
  <p>Darren - I have edited my code and used as you have specified in your answer:</p>
  
  <pre><code>Dim wrkSht As Worksheet
  Set wrkSht = ThisWorkbook.Worksheets("Sheet1")
  
  Dim resSheet As Worksheet
  Set resSheet = ThisWorkbook.Worksheets("Demo")
  
  With wrkSht
      .Range(.Cells(DataStartRow - 1, 1), .Cells(DataStartRow - 1, 4)).Copy
  End With
  
  With resSheet
      .Cells(9, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      .Cells(9, 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  End With
  </code></pre>
  
  <p>But still it is giving number as the format to my cells instead of the custom one that is assigned to cell in Sheet1.</p>
  
  <p>Can you please help if I am missing out something in this code ?</p>