Macro won't paste a copied cell

[ESH]

The following macro breaks at the line "cell.PasteSpecial Paste:=xlPasteAll". Why?

Sub PasteCopiedCell()

'Declare variables
Dim cell As range
Dim range As range
Dim targetCell As range

'Prompt user to select a cell
Set cell = Application.InputBox("Select a cell to copy:", "Copy Cell", Type:=8)

'Copy the cell
cell.Copy

'Prompt user to select a range
Set range = Application.InputBox("Select a range to paste the cell into:", "Paste Range", Type:=8)

'Prompt user to select a cell
Set targetCell = Application.InputBox("Select a cell to paste the cell into:", "Paste Cell", Type:=8)

'Loop through the range
For Each cell In range
  'If the cell on the same row in the targetCell column is not empty, then paste the copied cell
  If cell.Offset(0, targetCell.Column - cell.Column).Value <> "" Then
    cell.PasteSpecial Paste:=xlPasteAll
  End If
Next cell

End Sub

 

[nemmi69]

Is this better?

Sub PasteCopiedCell()

'Declare variables
Dim cell As range
Dim Tcell As range
Dim Trange As range
Dim targetCell As range

'Prompt user to select a cell
Set cell = Application.InputBox("Select a cell to copy:", "Copy Cell", Type:=8)

'Copy the cell
cell.Copy

'Prompt user to select a range
Set Trange = Application.InputBox("Select a range to paste the cell into:", "Paste Range", Type:=8)

'Prompt user to select a cell
Set targetCell = Application.InputBox("Select a cell to paste the cell into:", "Paste Cell", Type:=8)

'Loop through the range
For Each Tcell In Trange
  'If the cell on the same row in the targetCell column is not empty, then paste the copied cell
  If Tcell.Offset(0, targetCell.Column - cell.Column).Value <> "" Then
    Tcell.Value = cell.Value
  End If
Next Tcell
End Sub

 

[ESH]

Almost... that pastes the value into the correct cells, but I need it to paste the formula. I was able to fix my original macro by moving "cell.Copy" to just before the FOR loop. It works now, but I don't understand why the original doesn't.

 

[MARK858]

but I don't understand why the original doesn't.

Your selecting the ranges is probably taking it out of copy mode