title: paste to active cell VBA excel
body: <p>I need to paste text from a Word table to an Excel worksheet, but because some of the cells in Word have line breaks that I need to preserve in Excel, a straight copy/paste is not an option--Excel interprets any line breaks as cell breaks. To work around this, I'm replacing the line breaks in Word with dummy text (e.g., "@@@"), then pasting into Excel, and finally running find/replace to replace the dummy text with an Excel-compatible line break (alt+0010).</p>
  
  <p>I recorded a macro to do this:</p>
  
  <pre><code>Sub IDA()
  '
  ' IDA Macro
  '
  ' Keyboard Shortcut: Ctrl+q
  '
  Sheets("Deployment").Select
  ActiveWindow.SmallScroll Down:=-12
  Range("C2").Select
  ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
      False, NoHTMLFormatting:=True
  Selection.Replace What:="@@@", Replacement:="" &amp; Chr(10) &amp; "", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=False
  End Sub
  </code></pre>
  
  <p>But when I move the the next block of text to paste, it pastes back to the original destination. I can see the sheet ("Deployment") and range ("C2") are both hard-coded. I tried removing those lines, but that broke the script--nothing happens when I run it.</p>
  
  <p>I could simplify the script to just do the search and replace, but I don't trust the other users to use the paste special option when they paste into Excel, which means the spreadsheet may end up with other formatting from Word that I don't want.</p>
  
  <p>How do I modify my script so it simply pastes into the active worksheet and cell?</p>