Paste Special - Run-Time Error 1004

[Small Paul]

Hi

I am having difficulty with a Paste Special line in my VBA and cannot work out why. I have searched various websites and tried a load of different things, without success!

The section of code is:

Worksheets("Required Data").Activate
ActiveCell.Offset(0, 2).Copy
detail = ActiveCell.value
Sheets(detail).Select
ActiveCell.Offset(-2, 1).PasteSpecial xlPasteValues

The correct cell is being copied. The correct worksheet is selected. The 'Offset' selects the right cell.

Unfortunately it gives a Run-time error 1004: PasteSpecial method of Range class failed.

Any suggestions would be appreciated.

Many thanks
Small Paul.

 

[MARK858]

Any sheet/workbook protection or merged cells and do you get the same result with the code below?

What do you believe the activecell is on Sheets(detail)?

Is detail the name of the sheet, if it is it should have quotes i.e. Sheets("detail").

 

[Small Paul]

I knew it would be something simple! It was pasting to a merged cell.
Thank you very much Mark858

 

[MARK858]

Glad you resolved it but your code would be more efficient if you didn't have the Selects but to amend the ode then you need to answer my previous question...

What do you believe the activecell is on Sheets(detail)?

and the same question for Worksheets("Required Data")

 

[Small Paul]

The "Required Data" sheet is driven by columns A&B. Column A is defined as "detail" and and has a corresponding worksheet (set up via VBA according to criteria for A&B). So, for example, cell A2 has 1 in it. That piece of code looks at the value and goes to the worksheet named 1.

Now I have removed the Merge Cells command from the 1st macro, i can paste correctly. I now just have to work out how to Merge Activecell to column K!

 

[MARK858]

Much as I hate merged cells (better to use center across selection)...

I now just have to work out how to Merge Activecell to column K

Range(ActiveCell, Cells(ActiveCell.Row, "K")).Merge

or

Sub xxxx()
    With Range(ActiveCell, Cells(ActiveCell.Row, "K"))
        .HorizontalAlignment = xlCenter
        .Merge
    End With
End Sub

Center across selection option

Range(ActiveCell, Cells(ActiveCell.Row, "K")).HorizontalAlignment = xlCenterAcrossSelection

 

[Small Paul]

Many thanks. That looks tidier than the version I came up with!

Range(cells(ActiveCell.row, "C"), cells(ActiveCell.row, "K")).MergeCells = True

.HorizontalAlignment = xlLeft

 

[MARK858]

Try the extra option that I put in as my last edit