Cut, Paste and Move to next Row

[Guest]

Hello,

I some VBA code which I will post below. What I am trying to do is cut all
rows that meet the criteria of having the word "NONE" in the row and paste
that row onto a new worksheet that is created by the code. I am able to
create the new worksheet; go back to the existing sheet and sort it in
ascending order. The code will then run without errors and will cut all of
the rows containing "NONE" like I want it to; but when it pastes the just cut
row onto the new worksheet it keeps pasting it into the first row;
overwriting the data that was there. When the code is finished running all of
the rows that I want to have cut are gone from the existing sheet but the
only row I have pasted on the new sheet is the last row that was cut and
pasted. How can I get the code to move down one row each time it needs to
paste the most recent cut row? I have tried some things with the
ActiveCell.Next.Select method but when I add that in to the code I receive
errors. Any help with this will be greatly appreciated. Here is the code
below:

Private Sub cmdNoneTab_Click()

'Code below from the first Dim statement until the first End With statement
'will create a new Worksheet named Disbursement = NONE. If the command
'button is pushed unnecessarily a MsgBox will be displayed stating that a
'sheet with the same name has already been created.

Dim ws As Worksheet
Dim newSheetname As String
newSheetname = "Disbursement = NONE"
For Each ws In Worksheets
If ws.Name = newSheetname Or newSheetname = "" Or
IsNumeric(newSheetname) Then
MsgBox "Sheet already exists", vbInformation
Exit Sub
End If
Next
Sheets.Add Type:="Worksheet"
With ActiveSheet
.Move After:=Worksheets(Worksheets.Count)
.Name = newSheetname
End With

Worksheets("InsuranceDataForLoanPortfolio").Activate 'Re-Activate the
InsuranceDataForLoanPortfolio
'Tab after creating the
new Worksheet.
Cells.Select 'Select all the cells on the active Worksheet

'Code in section below will sort column "S" in Ascending order.
Selection.Sort Key1:=Range("S2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select

For Each Cell In Range("DisbMonthData") 'from here down is the trouble
If Cell.Value = "NONE" Then
Cell.EntireRow.Cut
Sheets("Disbursement = NONE").Select
ActiveSheet.Paste
End If
Next Cell

End Sub

Thank you for your assistance.

Dave Y

 

[Tom Ogilvy]

For Each Cell In Range("DisbMonthData") 'from here down is the trouble
If Cell.Value = "NONE" Then
Cell.EntireRow.Cut
Sheets("Disbursement = NONE").Select
cells(rows.count,1).End(xlup)(2).Activate
ActiveSheet.Paste
End If
Next Cell
Sheets("Disbursement = NONE").rows(1).Delete

 

[Guest]

Hi Tom,

Thank you for your prompt reply. I am receiving a 'Run-Time Error 1004' when
I run the code. If I Debug it, it stops on this line:
Cells(Rows.Count, 1).End(xlUp)(2).Activate
I'm getting somewhat better at VBA but I am still a rookie so I don't fully
understand this line of code or how to fix it. Actually what I don't
understand is the (2) after the End(xlUp). If you could be so kind to clarify
that for me I would greatly appreciate it. Also any suggestions to resolving
this error created by this line of code would be great. Thank You.

Dave Y

 

[Tom Ogilvy]

My oversight. I didn't notice you were running this as the click event of a
button. In that case:

For Each Cell In Range("DisbMonthData") 'from here down is the trouble
If Cell.Value = "NONE" Then
Cell.EntireRow.Cut
With Sheets("Disbursement = NONE")
.Select
.cells(rows.count,1).End(xlup)(2).Activate
End With
ActiveSheet.Paste
End If
Next Cell
Sheets("Disbursement = NONE").rows(1).Delete

Cells(rows.count,1).end(xlup) finds the last occupied cell in column 1
It is the same as going manually to the bottom of column 1 and hitting the
End key and then the up arrow
the (2) means to step down one cell so it refers to the next empty cell in
the column.

It is short for

Range("A1").Item(2) as an example

demo'd from the immediate window:

? range("A1").Item(2).Address
$A$2

similar would be to say it simulates the cells command at the end of the
range

? range("A1").Cells(2).Address
$A$2