PDA

View Full Version : Solved: Paste block of data into certain cells



Klartigue
06-06-2012, 02:17 PM
Please see the attached sheet. I am trying to copy all rows that dont have "Core" or "Core (IRA)" in cells in Column A and place these rows in G2. Sheet 1 of my sheet has the raw data and sheet 2 shows how I would like the data to end up after running the macro. Here is a start of the macro but it does not work how I would like it to work.

Any ideas?

Sub CorevsMuni()

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow

If .Cells(i, "A").Value = "Muni" Or _
.Cells(i, "A").Value = "Taxable Bonds" Or _
.Cells(i, "A").Value = "Taxable Bonds (IRA)" Or _
.Cells(i, "A").Value = "Short Duration Taxable" Then

With Range(.Cells(i, "A"), .Cells(i, "F")).Select
Selection.Cut
Sheets("Sheet1").Select
Range("G").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Select

End With

End If

Next i

End With

End Sub

Klartigue
06-06-2012, 02:17 PM
I forgot to attach my sheet, here it is

Klartigue
06-06-2012, 02:37 PM
I figured it out!


Sub CorevsMuni()

Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow

If .Cells(i, "A").Value = "Muni" Or _
.Cells(i, "A").Value = "Taxable Bonds" Or _
.Cells(i, "A").Value = "Taxable Bonds (IRA)" Or _
.Cells(i, "A").Value = "Short Duration Taxable" Then

With Range(.Cells(i, "A"), .Cells(i, "F")).Select
Selection.Cut
Sheets("Sheet1").Select
Range("G10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste


End With

End If

Next i

End With

End Sub