Klartigue
06-26-2012, 08:19 AM
I have this macro that pulls cuts data from one sheet and pastes it into another sheet.
Sub DataSort()
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 14 To lastrow
If .Cells(i, "B").Value = "Core" Or _
.Cells(i, "B").Value = "Core (IRA)" Or _
.Cells(i, "B").Value = "Muni" Or _
.Cells(i, "B").Value = "Taxable Bonds" Or _
.Cells(i, "B").Value = "Taxable Bonds (IRA)" Or _
.Cells(i, "B").Value = "Short Duration Taxable" Then
With Range(.Cells(i, "B"), .Cells(i, "O")).Select
Selection.Cut
Sheets("Sheet2").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End With
End If
Next i
End With
End Sub
I need to run the above code 4 separate times. However, if I run it as is, the second time i run it, the new data that is cut is obviously going to paste right over the present data. So is there a way to have this cut the data, and then in sheet 2 paste it in the next available row? So somehow say paste range is lastrow+1?
Sub DataSort()
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 14 To lastrow
If .Cells(i, "B").Value = "Core" Or _
.Cells(i, "B").Value = "Core (IRA)" Or _
.Cells(i, "B").Value = "Muni" Or _
.Cells(i, "B").Value = "Taxable Bonds" Or _
.Cells(i, "B").Value = "Taxable Bonds (IRA)" Or _
.Cells(i, "B").Value = "Short Duration Taxable" Then
With Range(.Cells(i, "B"), .Cells(i, "O")).Select
Selection.Cut
Sheets("Sheet2").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End With
End If
Next i
End With
End Sub
I need to run the above code 4 separate times. However, if I run it as is, the second time i run it, the new data that is cut is obviously going to paste right over the present data. So is there a way to have this cut the data, and then in sheet 2 paste it in the next available row? So somehow say paste range is lastrow+1?