title: VBA script to copy Data from specfic cell range and paste to a specific cell in a new sheet
body: <p>I have the following script and am trying to work out how to paste the copied data to cell AA1 in sheet TEST, But before it pastes the selected data i need to clear all the data that is in columns AA:AK in sheet TEST. The script copies the data ok but i can't get Selection.PasteSpecial to work with "offset", and i cant work out how to clear the contents of AA:AK in advance.
  Any help would be much appreciated.</p>
  
  <p>Sub CopyDATA()</p>
  
  <pre><code>' Set Auto Filter
  Selection.AutoFilter
  Sheets("SBC_Month").Select
  Range("$AA$1:$AK$5000").AutoFilter Field:=9, Criteria1:="Rep Name"   ' Filters by Rep Name
  
  ' copy filtered data
  Range("AA1").Select
  Range(Selection, Selection.End(xlToRight)).Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Copy
  
  ' Paste to sheet Test
  Sheets("TEST").Select
  Selection.PasteSpecial , Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  </code></pre>
  
  <p>End Sub</p>