Copy, paste value, to new sheet

[jrwrangler]

Hello

I have a sheet called lpl data. I need to look over at the cells in column j. If the cell in column j has the words "common stock", then i need to copy that entire row, delete that row, and paste it into another sheet called "______ portfolio". The " _____" will depend on the input provided by the user at the beginning of the macro (already coded that up). Column J is the column to the furthest right in the sheet. The new sheet will start taking this data in cell b3. I need to repeat this process for other values in this column and others so if you could please dumb it all down for me so i could do this on my own, i would really appreciate it. thanks

this is how far i got

Sub Copypaste()

Application.ScreenUpdating = False

lr = Sheet("LPL Data").Range("j" & Rows.Count).End(xlUp).Row
For i = 1 To lr
With Sheet("LPL Data")
If LCase(.Range("j" & i).Value) = "Common Stock" Then
lrr = Sheets("*" & " portfolio").Range("B" & Rows.Count).End(xlUp).Row

Range("J", Selection.End(xlToLeft)).Select
Selection.Copy Sheets("*" & " portfolio").Range("b" & lrr)

End If
End With

Next i

Application.ScreenUpdating = True

 

[Dryver14]

lrr = Sheets("*" & " portfolio").Range("B" & Rows.Count).End(xlUp).Row

If this line is going to be B3 every time then just say so otherwise you need +1 at the end of the description
so it finds the last used row and then steps one down to paste.

 

[jrwrangler]

I run the macro and i get "sub or function not defined"
In this line:
lr = Sheet("LPL Data").Range("j" & Rows.Count).End(xlUp).Row

its highlighting the "sheet".

 

[Dryver14]

firstly it should say sheets

if you have option explicit you need to dim all your variables

i, lr and Irr

 

[jrwrangler]

Ok, well the code runs without error but nothing is actually happening. Any ideas?

 

[Dryver14]

untested:

Application.ScreenUpdating = False
lrr = Sheets("*" & " portfolio").Range("B" & Rows.Count).End(xlUp).Row + 1
lr = Sheet("LPL Data").Range("j" & Rows.Count).End(xlUp).Row
For i = lr to 1 step -1
If LCase(.Range("j" & i).Value) = "Common Stock" Then
Range("j" & i).EntireRow.Copy Destination:=Sheets("*" & "portfolio").Cells(lrr, 2)
Range("j" & i).EntireRow.Delete
lrr = lrr + 1
End If
Next i
Application.ScreenUpdating = True

 

[Dryver14]

you should note that the macro runs from bottom to top due to sheets being deleted, I am not sure what this does to the data being transfered.

If it needs to be in top to bottom order it may need to run twice once to copy info once to delete the line

 

[jrwrangler]

Well thanks for trying. that didnt work either.

 

[Dryver14]

try placing this line just beneath the application.screenupdating = false line

ensure you are on LPL Data sheet and press f8 to step through and ensure it is recognising the portfolio sheet

Sheets("*" & " portfolio").select

did you mean for the gap between the first appostophie and the word Portfolio?

 

[jrwrangler]

Ya "im out of range" or something. its highlighting the lrr line where i define the variable

I did mean for the gap because there will be a gap between the first word and portfolio