Reply to: Excel Experts - Stuck Again

[LisaContractorUmbrella]

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

Nope just pure style

[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]

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

[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'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]

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]

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
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]

Hi Eek

Thanks for that - we are now ending up in the right place Trouble is the paste is not working - possibly as the data is on a different worksheet. Macro as it stands at the moment is:

Sub Macro1()
'
' Macro1 Macro
'

'
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.

[eek]

It gets difficult selecting the cells outside the range as logic is slightly backwards.

From memory its

Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row +1).Select

but its probably better to do something like this as its clearer

NewCell= Cells(Cells.Rows.Count, "A").End(xlUp).Row +1
Range("A" & NewCell).Select

as that is clearer. mind you vba is as clear as mud most of the time.

ActiveSheet.Paste would then paste the contents of the clipboard into the spreadsheet.

[LisaContractorUmbrella]

Thanks Tony - this is really useful. Think I am getting there but am having to write the code rather than recording now and it is tricky I have the macro placing the cursor in the correct cell and I have highlighted the area I need from the second worksheet. Trouble is I now don't know how to write the code that will paste it in the area which starts 1 cell down from the last active cell.

[BoredBloke]

Do this

set a variable for your row number

dim lLastRow as long

use this to determine the last used row

lLastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirecti on:=xlPrevious).Row

You can then use this value in any range selection thing

eg range(cells(2,1),cells(llastrow,5)).select
would select cells(2,1) = A_2 to cells(llastrow,5) = e_LastRow

[LisaContractorUmbrella]

Not an expert but try

Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Select

That worked perfectly Thanks Everyone

[LisaContractorUmbrella]

Thanks for coming back so quickly guys - think a bit more info will help. Two reports are run each week which will have the same number of columns and a different number of rows - both reports are then exported into Excel and fiddled about with until they are in the format that we need.I then need to amalgamate the data on both reports so that the whole lot can be sorted. I want to do all this with a macro as it is a regular excercise and time consuming. Trouble is I can't find a way of automatically directing the cursor to the last row of the first report so that I can copy and paste the data from the second report directly beneath it so that it can be sorted.

Although I am happy to record macros my knowledge of the code is slightly less than would fit on the average size postage stamp but if I have understood correctly the solutions so far would identify the last cell but not take the cursor there

[eek]

Not an expert but try

Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Select