PDA

View Full Version : Solved: Copy and Paste in next available row



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?

Bob Phillips
06-26-2012, 08:24 AM
Surely, the fact that your code is looking back up to the last used row and offsetting one row means it will always go into a new row.

Did you try it?

Klartigue
06-26-2012, 08:53 AM
Yes, and it doesnt seem to be working..here is the code. Sub CreateSheet1()
'
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Core").Select
End Sub
Sub DataSortCore()

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 = "Fixed Income" 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("Sheet1").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Core").Select

End With

End If

Next i

End With

End Sub
Sub GotoCoreEIP()
Sheets("Core-EIP").Select
End Sub
Sub DataSortCoreEIP()

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 = "Equity" Or _
.Cells(i, "B").Value = "Equity Income" Or _
.Cells(i, "B").Value = "Fixed Income" Then


With Range(.Cells(i, "B"), .Cells(i, "O")).Select
Selection.Cut
Sheets("Sheet1").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Core-EIP").Select

End With

End If

Next i

End With

End Sub

And when I try to run Sub DataSortCoreEIP, nothing happens

Klartigue
06-27-2012, 01:26 PM
It works, it wasnt working because Equity had three spaces after it in the cell so i should have referenced "Equity ". And same with the other cell values.

Bob Phillips
06-27-2012, 02:13 PM
You could always use

If .Cells(i, "B").Value = Trim("Equity") Or _
.Cells(i, "B").Value = Trim("Equity Income") Or _
.Cells(i, "B").Value = Trim("Fixed Income") Then