|There's a lot wrong with your code, starting with a number of syntax errors.|
1 - Sheet2!Cells(1, 1) is not correct.
If Sheet2 is the second sheet in the workbook, counting from left to right, then the correct syntax would be:
If you want to refer to the sheet by name, regardless of where it is positioned in the workbook, the correct syntax would be:
2 - You do not have a space before the Line Continuation character (the underscore).
Replacement:=Sheets(2).Cells(1, 2), _
3 - In some parts of your code you refer to specific cells (Cells(1, 1), Sheets(2).Cells(1, 1), etc) yet you are doing your replacement in the Selected cell.
That will give you the result you asked for in your example only if Cells(1, 1) is the current Selection.
Since you rarely have to actually Select a range in VBA to perform an action on it, it might be better to use:
4 - So, after you fix all of the syntax errors and actually get the code to run, you are going to find that your end result in Cells(1, 1) will be 2, not book 2 bag because you are using:
Selection.Replace What:=Cells(1, 1),
which tells VBA to replace the entire contents of the cell.
What you probably want to use is:
Cells(1, 1).Replace What:=Sheets(2).Cells(1, 1),
By making the changes noted above I was able to get the result you are looking for.
I'll leave it up to you to try and fix your code by yourself. It'll be a great learning experience.
P.S. Please click on the following line and read the instructions found via that link before posting anymore code in this forum. Thanks!
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.