title: Excel Copy Paste Macro Offsetting Incorrectly
body: <p>I am using this macro to paste values and formatting into another workbook. It mostly works ok but I am having a problem with the lines pasting into Sheet1.</p>
  
  <p>Instead of pasting the values, formatting and then resizing the font the code pastes the values then on the next blank row pastes the formatting then on the next blank row resizes the font.</p>
  
  <p>I want the values, formatting and font resize to work on the same rows.</p>
  
  <p>The macro runs from a button. A user will select options then click the button to run it multiple times. The idea is that Sheet1 will have a list of multiple entries.</p>
  
  <p>Can someone point out what I'm doing wrong?</p>
  
  <p>Thanks</p>
  
  <pre><code>    Sub CreateNewSheet()
  Application.ScreenUpdating = False
  Application.Calculation = xlManual
  
      With Workbooks("Batsmen.xlsx").Worksheets.Add()
      .Name = Range("E3").Value
      End With
  
  Sheets("Summary").Range("A22:J63").Copy
  Workbooks("Batsmen.xlsx").Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
  Workbooks("Batsmen.xlsx").Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteFormats
  Workbooks("Batsmen.xlsx").Sheets(1).Range("A:J").Font.Size = 10
  
  Sheets("Summary").Range("A22:J27").Copy
  Workbooks("Batsmen.xlsx").Sheets("Sheet1").Range("A" &amp; Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
  Workbooks("Batsmen.xlsx").Sheets("Sheet1").Range("A" &amp; Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteFormats
  Workbooks("Batsmen.xlsx").Sheets("Sheet1").Range("A" &amp; Rows.Count).End(xlUp).Offset(1).Font.Size = 10
  
  Application.Calculation = xlAutomatic
  Application.ScreenUpdating = True
  End Sub
  </code></pre>