Excel Experts - Stuck Again

[eek]

Hi Eek
Sub Macro1()

Columns("F:F").Select
Selection.Cut Destination:=Columns("N:N")
Range("F1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[8],LEN(RC[8])-2)"
Range("F1").Select
Selection.AutoFill Destination:=Range("F1:F1015"), Type:=xlFillDefault
Range("F1:F1015").Select
Range("A1").Select
Sheets("Daybook Invoices").Select
Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Select
NewCell = Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Range("A" & NewCell).Select
Sheets("Daybook Credits").Select
Range("A1:M1015").Select
ActiveSheet.Paste
End Sub

Comes up with a runtime error 1004 - Paste method of Worksheet class failed. Tricky little blighter!

Any suggestions gratefully received.

As its excel you really only want a single cell to be selected when you hit paste otherwise you can be sure the size of the ranges won't match and you get an error. I would be tempted to try.

Range("A1").Select
ActiveSheet.Paste


I would also say that I don't think that there is a lot of code in that macro that probably won't do what you think it will but that will only be confirmed once you've got the paste working.

[LisaContractorUmbrella]

As its excel you really only want a single cell to be selected when you hit paste otherwise you can be sure the size of the ranges won't match and you get an error. I would be tempted to try.

Range("A1").Select
ActiveSheet.Paste


I would also say that I don't think that there is a lot of code in that macro that probably won't do what you think it will but that will only be confirmed once you've got the paste working.

Hi Eek

Nope no luck unfortunately - came up with same error. The rest of it I have tested and it works ok it's just this last little bit - problem is that the 'cut' area will always be a different size and the 'paste' area will never start in the same cell

[eek]

Hi Eek

Nope no luck unfortunately - came up with same error. The rest of it I have tested and it works ok it's just this last little bit - problem is that the 'cut' area will always be a different size and the 'paste' area will never start in the same cell

I'm not helped by the fact that I only have a very recent version of excel around.

But try changing the paste to be

Range("A1016").Select
Sheets("Daybook Credits").Paste

and see if that allows the paste to work.

[LisaContractorUmbrella]

I'm not helped by the fact that I only have a very recent version of excel around.

But try changing the paste to be

Range("A1016").Select
Sheets("Daybook Credits").Paste

and see if that allows the paste to work.

I know - am trying to get used to 2007 myself. Still didn't work I'm afraid

[eek]

I know - am trying to get used to 2007 myself. Still didn't work I'm afraid

I think you may need to cut the macro down and work through it step by step as this works


'select the cells we want to copy
Range("a1:b1").Select
Selection.Copy
'move to the worksheet we want the cells on
Sheets("Sheet2").Select
'Move to the correct location on that page (note we only 'want the selected range to be a single cell)
NewCell = Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Range("A" & NewCell).Select
' paste the cells
ActiveSheet.Paste

I wander if you have anything in the clipboard when you get to the paste request.

[LisaContractorUmbrella]

I think you may need to cut the macro down and work through it step by step as this works


'select the cells we want to copy
Range("a1:b1").Select
Selection.Copy
'move to the worksheet we want the cells on
Sheets("Sheet2").Select
'Move to the correct location on that page (note we only 'want the selected range to be a single cell)
NewCell = Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Range("A" & NewCell).Select
' paste the cells
ActiveSheet.Paste

I wander if you have anything in the clipboard when you get to the paste request.

Yay - it worked Thank you so much Eek - really appreciated

[northernladuk]

Yay - it worked Thank you so much Eek - really appreciated

Just hope the free accountancy advice you are going to get doesn't involve an excel spreadsheet!!!

[LisaContractorUmbrella]

Just hope the free accountancy advice you are going to get doesn't involve an excel spreadsheet!!!

Nope just pure style