Solved: Problem with cut/paste to last row of sheets

[CatDaddy]

[VBA]Sub WorkDivision_SortOthers()
Dim MB As Workbook
Set MB = Workbooks("MasterWorkbook.xls")
MB.Sheets(1).Activate
For Each cell In Range("F:F")

Select Case cell.Value

Case "AAA":
cell.EntireRow.Cut Destination:=Sheets(8).Cells(Rows.Count, 2).End(xlUp).Row
Case "BBB":
cell.EntireRow.Cut Destination:=Sheets(4).Cells(Rows.Count, 2).End(xlUp).Row
Case "CCC":
cell.EntireRow.Cut Destination:=Sheets(6).Cells(Rows.Count, 2).End(xlUp).Row

Case "DDD":
cell.EntireRow.Cut Destination:=Sheets(2).Cells(Rows.Count, 2).End(xlUp).Row

Case "EEE":
cell.EntireRow.Cut Destination:=Sheets(5).Cells(Rows.Count, 2).End(xlUp).Row

Case "FFF":
cell.EntireRow.Cut Destination:=Sheets(4).Cells(Rows.Count, 2).End(xlUp).Row


End Select
Next cell
End Sub
[/VBA]

I'm trying to get this macro to go through the first worksheets and send entire rows to the end of different worksheets based on the select case, but it doesnt send anything...something wrong with cut

[Blade Hunter]

This doesn't fix you probem but you probably want to offset that destination by 1 row otherwise you will overwrite the existing last row of data

[shrivallabha]

First guess:
[VBA]cell.EntireRow.Cut Destination:=Sheets(8).Cells(Rows.Count, 2).End(xlUp).Row [/VBA]
is incorrect and it should be cell like (as marked by blade hunter):
[VBA]cell.EntireRow.Cut Destination:=Sheets(8).Cells(Rows.Count, 2).End(xlUp)(2)[/VBA]

[CatDaddy]

no...that doesn't work...

Since I am copying an entire row shouldnt the destination be a row?

[shrivallabha]

No, thats incorrect. Row returns Row Number and NOT Row Object.

Following is the correct syntax
[VBA]Case "AAA":
Rows(cell.Row).Cut (Sheets(8).Cells(Rows.Count, 2).Offset(, -1).End(xlUp)(2))[/VBA]

This errors because Excel tries to paste the whole row starting from column 2 and gets spillover.

[CatDaddy]

is the (2) at the end what is giving it the error? I dont understand what you are trying to say and it still doesnt work

[shrivallabha]

Strange, it shouldn't give any error. I've posted it after testing. (2) ensures that the data is written on the Next Line otherwise the data will keep overwriting on the same row!

Post your workbook then.

[Blade Hunter]

Can you please paste this:

[vba]
?Sheets(8).Cells(Rows.Count,2).Offset(, -1).End(xlUp)(2).address
[/vba]

in the debug window (CTRL-G) and press enter then tell us what the result is.

Thanks

Dan

[Aflatoon]

This:

Sheets(8).Cells(Rows.Count, 2).Offset(, -1).End(xlUp)(2)

returns the cell below the last used row in column A on sheet 8. Based on the initial post I suspect you want:

Sheets(8).Cells(Rows.Count, 2).End(xlUp).offset(1, -1)

which will find the last used row in column B then go one row down and one column left. It also should not be in parentheses.

So, if we assume that the target sheet is also in MB (as it was not specified in the original code):
[vba]Sub WorkDivision_SortOthers()
Dim MB As Excel.Workbook
Dim cell As Excel.Range

Set MB = Workbooks("MasterWorkbook.xls")

For Each cell In MB.Sheets(1).Range("F:F")

Select Case cell.Value

Case "AAA"
cell.EntireRow.Cut Destination:=MB.Sheets(8).Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)
Case "BBB"
cell.EntireRow.Cut Destination:=MB.Sheets(4).Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)
Case "CCC"
cell.EntireRow.Cut Destination:=MB.Sheets(6).Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)

Case "DDD"
cell.EntireRow.Cut Destination:=MB.Sheets(2).Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)

Case "EEE"
cell.EntireRow.Cut Destination:=MB.Sheets(5).Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)

Case "FFF"
cell.EntireRow.Cut Destination:=MB.Sheets(4).Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)
End Select
Next cell
End Sub
[/vba]

[shrivallabha]

Thanks Aflatoon. Apologies for my "Lost In Transfer" drivel.

And Yes, it will work if Column A is also NON-BLANK as Column B but that will be BAD coding anyway!

[CatDaddy]

I see...its because it was in the second column that the row wouldnt paste (because there wasnt enough room for the row + another cell huh?)...seems like Aflatoon got it spot on for me! thanks everyone