Copy and paste into active cell

[londa_vba]

Hello
I want to be able to choose any cell in column A (A9) on active sheet "CE" and have a macro button then copy ranges "B8:B33,B39:B64,B70:B95,B101:B126" from sheet "YPF" and paste them into active sheet active cell plus 27(?) columns to the right (AA9) on sheet ("CE")

 

[DanteAmor]

If you want to copy everything:

Sub Copy_and_paste_into_active_cell()
  If ActiveCell.Column <> 1 Then
    MsgBox "Select a cell in column A"
    Exit Sub
  End If
  Sheets("YPF").Range("B8:AC33,B39:AC64,B70:AC95,B101:AC126").Copy ActiveCell
End Sub

Or simply values:

Sub Copy_and_paste_into_active_cell_V2()
  If ActiveCell.Column <> 1 Then
    MsgBox "Select a cell in column A"
    Exit Sub
  End If
  Sheets("YPF").Range("B8:AC33,B39:AC64,B70:AC95,B101:AC126").Copy
  ActiveCell.PasteSpecial xlPasteValues
End Sub

Regards
Dante Amor

 

[londa_vba]

If you want to copy everything:

Sub Copy_and_paste_into_active_cell()
  If ActiveCell.Column <> 1 Then
    MsgBox "Select a cell in column A"
    Exit Sub
  End If
  Sheets("YPF").Range("B8:AC33,B39:AC64,B70:AC95,B101:AC126").Copy ActiveCell
End Sub

Or simply values:

Sub Copy_and_paste_into_active_cell_V2()
  If ActiveCell.Column <> 1 Then
    MsgBox "Select a cell in column A"
    Exit Sub
  End If
  Sheets("YPF").Range("B8:AC33,B39:AC64,B70:AC95,B101:AC126").Copy
  ActiveCell.PasteSpecial xlPasteValues
End Sub

Regards
Dante Amor

Hello Dante, the message box prompt is awesome but I need the copy range to stay B8:B33,B39:B64,B70:B95,B101:B126 from "YFP" (this wont ever change) and the paste to be in "CE" sheet using the row of the selected cell but over in AA column because I have some hidden formulas out there. So if I select A12 I want it to paste into AA12, if I chose A47 I want it to paste into AA47, etc. I hope that explanation is easier.

 

[DanteAmor]

So if I select A12 I want it to paste into AA12, if I chose A47 I want it to paste into AA47

Try:

Sub Copy_and_paste_into_active_cell()
  If ActiveCell.Column <> 1 Then
    MsgBox "Select a cell in column A"
    Exit Sub
  End If
  Sheets("YPF").Range("B8:B33,B39:B64,B70:B95,B101:B126").Copy Range("AA" & ActiveCell.Row)
End Sub

 

[londa_vba]

Try:

Sub Copy_and_paste_into_active_cell()
  If ActiveCell.Column <> 1 Then
    MsgBox "Select a cell in column A"
    Exit Sub
  End If
  Sheets("YPF").Range("B8:B33,B39:B64,B70:B95,B101:B126").Copy Range("AA" & ActiveCell.Row)
End Sub

I am getting a subscript out of range message for line

Sheets("YPF").Range("B8:B33,B39:B64,B70:B95,B101:B126").Copy Range("AA" & ActiveCell.Row)

 

[DanteAmor]

Check that you have copied the macro correctly:

Sub Copy_and_paste_into_active_cell()
  If ActiveCell.Column <> 1 Then
    MsgBox "Select a cell in column A"
    Exit Sub
  End If
  Sheets("YPF").Range("B8:B33,B39:B64,B70:B95,B101:B126").Copy Range("AA" & ActiveCell.Row)
End Sub

and the name of the sheet

 

[londa_vba]

Check that you have copied the macro correctly:

Sub Copy_and_paste_into_active_cell()
  If ActiveCell.Column <> 1 Then
    MsgBox "Select a cell in column A"
    Exit Sub
  End If
  Sheets("YPF").Range("B8:B33,B39:B64,B70:B95,B101:B126").Copy Range("AA" & ActiveCell.Row)
End Sub

and the name of the sheet

hmm now it says can't do that in a merged cell

 

[DanteAmor]

now it says can't do that in a merged cell

You cannot paste a range of cells into a merged cell.
Remove the merged cells and try again.

It is not recommended to use combined cells in data management. You can use them in titles or something like that.

If you don't remove them, you will have problems with any macro.