Copying specific cells based on cell value "P" or "O" to sht1 or sht2

I need some help copying cells f10,h10,f13,h13,j13,f16,h16,j16 as well as f20:k42 if cells are not blanks to two different sheets. To sheet1 if column k20:k42 value is "P", if value is "O" then copy all cells to sheet2 in rows. Always copy to next blank row. I have done a lot of searching and formulating to no avail
Can you please help?

What you're requesting sounds doable, but more details would be helpful. Can you clarify a few of the particulars? What sheet is the data being copied from? When the data is copied to sheet 1 or 2, what's the arrangement? Does F10 go to A1 (or column A of the next empty row), H10 to A2, etc? The most useful resource would be a sample workbook (Use Go Advanced > Manage Attachments) that demonstrates how your info looks and how you want it to look in the end.

Hi,

What i am tying to achieve is if column "K" in Input sheet value is "p" then copy the record to sheet5 but if column "K" value is "o" then copy to sheet6

for example if k20 value is "P" then
copying would entail copying cells f10 to j16 as well as for example f20:j20 to sheet5 but if
for example if k20 value is "O" then copy the above to sheet 6

this would repeat for each record, next record which copies would be f10 to j16 again but cells this time will be f21:j21 and so on to cells
f42:j42. seperating data

Can you help?

Thanks

The destinations still aren't clear - if I'm copying f10 to J16 and f20:j20 to sheet 5 (for example), what cells do they land in? I took a guess, and just arrayed your information across the row. Try the procedure below out and see if it works. You'll need to put your headings for sheets 5 and 6 in Row 1 and you'll need to tweak the formats on sheets 5 and 6 to your liking, but it should cleanly transfer your data across the rows. I tried to leave the structure of the VBA pretty clear so that you could make modifications as necessary. Let me know how it goes.

Please Login or Register  to view this content.

I have checked the formula it is working well, but i need to start the data copying on sheet5 and sheet from from cell A6.

Also is there a way of moving data from sheet5 to a new sheet Cancel, for example whichever rows a user selects and clicks cancel macro, the data moves in to Cancel sheet A6 until lastrow

Is there any data in Row 5 of Sheets 5 or 6?

No data in row 5 of both sheets, need space at the top for headings buttons etc

The version below should start at A6. As to your second request, how do you envision the selection occurring? Is the user selecting the entirety of the data he/she wants moved to the Cancel page (so basically a cut/paste)? Would you prefer an input box asking for the first and last row to be moved, then have the procedure move everything in between?

Please Login or Register  to view this content.

Thanks cantosh for the help, I have managed the cancel procedure by using selection.copy then cut/ paste method and then selection.delete

My pleasure, I'm glad you got it working

can someone help me with this scenario, i have a macro Cancel on sheet5, once a row is selected and the macro is clicked it should transfer the selected rows to the cancel sheet in the next available empty row till last row and delete from sheet5. both sheets are protected and must stay protected. once the macro runs i get error
1004: pastespecial method of range class failed. this is my code so far:

Sub Cancel()
Sheets("Sheet5").Activate
ActiveSheet.Unprotect Password:="lemon1"
Cells.Select
' unlock all the cells
Selection.Locked = False
Selection.Copy
Sheets("CANCEL").Activate
ActiveSheet.Unprotect Password:="lemon1"
Cells.Select
' unlock all the cells
Selection.Locked = False
ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
paste:=xlPasteValues
Application.CutCopyMode = False
Selection.Locked = True
ActiveSheet.Protect Password:="lemon1"
Sheets("Sheet5").Activate

Selection.Delete
Selection.Locked = True
ActiveSheet.Protect Password:="lemon1"

MsgBox "Holidays Have Been Cancelled", vbInformation
End Sub

Is this what you wanted?

Please Login or Register  to view this content.

i was looking to amend the Cancel Macro code, please refer to sheet5 the cancel button at the top

Is your cancel code working?

Your code says
Copy Sheet5.Cells and paste Cancel.Cells and Delete Sheet5.Cells

Is that what you want do do? or what?

I just clicked on other button and getting error...