Copy/Paste macro

[Matt_Straya]

Hi,
I have a code that I can select a cell then choose a cell to paste it into. However, I want to past it into "Report"!E36 but it wont do it. Any ideas?

Sub GoodNewsTool()
Dim rng, rnge As Range
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select a Good News story:", _
Title:="The Good News Tool", Type:=8)
Set rnge = Application.InputBox(Prompt:="Paste a Good News story:", _
Title:="The Good News Tool", Type:=8)
rng.Select
Selection.Copy
rnge.Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
&

_________________________________________________________________

Post moved by Moderator.

 

[PCosta87]

Hi,

Try it like this:

Sub GoodNewsTool()
Dim rng, rnge As Range
'On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select a Good News story:", _
Title:="The God News Tool", Type:=8)
Set rnge = Application.InputBox(Prompt:="Paste a Good News story:", _
Title:="The Good News Tool", Type:=8)
rng.Copy rnge
End Sub

Cheers

 

[Matt_Straya]

Cheers PCsta87 - that works! Now I feel foolish! haha!

 

[Matt_Straya]

Was working ok now this. hmmm. It would be good to have the selection go straight to Report!E36 from clicking OK in the first Inputbox though.

 

[PCosta87]

Hi,

Not sure why it is breaking... can you walk me through what you did exactly?

As for the selection, after the first inputbox seems impossible as you haven't selected the destination yet.
If you meant after the second, then:

Sub GoodNewsTool()
Dim rng, rnge As Range
'On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select a Good News story:", _
Title:="The God News Tool", Type:=8)
Set rnge = Application.InputBox(Prompt:="Paste a Good News story:", _
Title:="The Good News Tool", Type:=8)
rng.Copy rnge
rnge.Worksheet.Activate
rnge.Select
End Sub

To do it after the first inputbox, the only option is if you are always going to paste the data in the same sheet, in which case we can activate that sheet after the first "OK" by doing:

Sheets("Report").Activate

Hope this helps

 

[Matt_Straya]

Hi - Thanks. I have uploaded the workbook with some dummy data but the guts of it is there. Ultimately I would like a person to select one good news story from the "INPUT" sheet and with a click, it gets pasted automatically into the "Report" Sheet at E36. I have also hopefully attached a snip of the code in the viewer.

 

[Nebu]

Hi:

Do the following code do what you are looking for?

Sub GoodNewsTool()
Application.ScreenUpdating = False

Dim rng As Range

On Error Resume Next
Sheet1.Range("E36:O50").UnMerge

Set rng = Application.InputBox(Prompt:="Select a Good News story:", _
Title:="The God News Tool", Type:=8)

rng.Copy
Sheet1.[E36].PasteSpecial xlValues
Sheet1.Range("E36:O50").Merge

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Thanks

 

[Matt_Straya]

OMG! Perfect! Thank you! I was all tied up in knots. Brilliant! Thank you again