PDA

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



CatDaddy
07-26-2011, 05:01 PM
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


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
07-26-2011, 11:51 PM
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
07-27-2011, 08:46 AM
First guess:
cell.EntireRow.Cut Destination:=Sheets(8).Cells(Rows.Count, 2).End(xlUp).Row
is incorrect and it should be cell like (as marked by blade hunter):
cell.EntireRow.Cut Destination:=Sheets(8).Cells(Rows.Count, 2).End(xlUp)(2)

CatDaddy
07-27-2011, 09:59 AM
no...that doesn't work...

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

shrivallabha
07-27-2011, 10:15 AM
No, thats incorrect. Row returns Row Number and NOT Row Object.

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

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

CatDaddy
07-27-2011, 10:26 AM
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
07-27-2011, 10:42 AM
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
07-27-2011, 04:56 PM
Can you please paste this:


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


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

Thanks

Dan

Aflatoon
07-28-2011, 01:02 AM
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):
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

shrivallabha
07-28-2011, 06:36 AM
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
07-28-2011, 11:51 AM
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 :)