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).
I recorded a macro to do this:
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:="" & Chr(10) & "", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
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.
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.
How do I modify my script so it simply pastes into the active worksheet and cell?