Simple task

[LeeLee17]

I would like to copy two cells from sheet 1 ("Today") into sheet2 "Notation" if column O (15) in sheet 1 is not blank. In other words, Sheets("Today").Cells(i,15) <> "" Then
Copy Column A (1) and column O (15) from sheet1 "Today" into sheet2 "Notation" in cells( i, A ) and cells (i, B). It will loop through an unknown number of rows in column O (15). Would really appreciate simple code to get this done. Appreciate your knowledge and time. Thank you!

 

[Skybluekid]

Welcome to the board.

Just a quick question. Why will it be an unknown number of rows in Column O?

 

[LeeLee17]

Because the inputs vary on a daily basis. Thank you!

 

[LeeLee17]

Also, I should have mentioned that column O in "Today" is a data validation field...

 

[Peter_SSs]

Test this with a copy of your workbook. Assuming that I have understood correctly, it should copy all the relevant rows at once rather than checking every row one at a time.

Sub Copy_Values()
  With Sheets("Today")
    .AutoFilterMode = False
    With .Range("A1", .Range("O" & Rows.Count).End(xlUp))
      .AutoFilter Field:=15, Criteria1:="?*"
      If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
        Intersect(.Offset(1), .Range("A:A,O:O")).Copy Destination:=Sheets("Notation").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
      End If
    End With
    .AutoFilterMode = False
  End With
End Sub

 

[LeeLee17]

Thanks Peter, did you say that it copies the row? I actually need it to copy from "Today" (sheet1) only the relevant row for column A and column O where column O is not blank- and paste into "Notation" (Sheet2).

So, assuming there are entries in "Today" column O for the first 2 rows (taking into account the two rows used for headers), the cells A3 & O3 would be copied into "Notation" (here there is only one row for a header) and paste into A2 and B2. Then A4 & O4 into A3 and B3 in Notation...I greatly appreciate your time and effort. Thank you!

 

[Peter_SSs]

I suggest that you give the code a try and let us know what went wrong if it does not do what you want.

 

[LeeLee17]

Good morning, and thank you for taking the time to provide this code. Please let me inform that it does copy the entire row- which extends beyond column "O" and returns the following error message: Run-time error '1004' Application defined or object defined error. The yellow highlighted code starts with Intersect. Ultimately, I need to copy only cells from column A & O when O is not blank. The sheet to copy from is "Today" and the destination sheet is "Notation." Thanks for any kind assistance you can provide.

 

[LeeLee17]

To clarify the task is:

Copy two non-adjacent cells into another sheet when condition is met​

 

[Peter_SSs]

Please let me inform that it does copy the entire row- which extends beyond column "O" and returns the following error message: Run-time error '1004' Application defined or object defined error.

I can only imagine that you have changed the code somewhat or combined it with other code. Did you copy/paste the code or type it out yourself?
The code can be copied from the forum using this icon at the top right of the code window.

The code as posted above does not copy whole rows, it copies the values from column A and O only and pastes them in the Notation sheet.

Here is my sample Today sheet. You will notice that I have made certain varied formatting to the cells you want copied (Colour, Bold, Italics, Underline, font size, alignment.

LeeLee17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13	Hdr14	Hdr15	Hdr16	Hdr17	Hdr18
2	A2	Data 198	Data 1	Data 33	Data 426	Data 165	Data 781	Data 906	Data 476	Data 679	Data 606	Data 658	Data 648	Data 537	w	Data 692	Data 328	Data 596
3	A3	Data 300	Data 501	Data 701	Data 962	Data 840	Data 201	Data 968	Data 864	Data 178	Data 53	Data 524	Data 203	Data 411		Data 909	Data 321	Data 611
4	A4	Data 433	Data 968	Data 797	Data 446	Data 572	Data 153	Data 613	Data 996	Data 485	Data 645	Data 677	Data 168	Data 598	abc	Data 881	Data 565	Data 6
5	A5	Data 23	Data 337	Data 376	Data 996	Data 790	Data 599	Data 69	Data 349	Data 610	Data 227	Data 916	Data 86	Data 381		Data 657	Data 969	Data 928
6	A6	Data 520	Data 958	Data 489	Data 695	Data 937	Data 511	Data 68	Data 720	Data 572	Data 751	Data 735	Data 167	Data 318	kl	Data 855	Data 629	Data 568
7	A7	Data 855	Data 575	Data 722	Data 451	Data 958	Data 885	Data 87	Data 76	Data 329	Data 501	Data 215	Data 735	Data 636		Data 766	Data 273	Data 32
8	A8	Data 733	Data 423	Data 551	Data 330	Data 239	Data 360	Data 305	Data 275	Data 106	Data 783	Data 535	Data 403	Data 100		Data 597	Data 6	Data 256
9	A9	Data 17	Data 426	Data 635	Data 472	Data 823	Data 759	Data 499	Data 170	Data 326	Data 564	Data 699	Data 33	Data 726	z	Data 622	Data 367	Data 167
Today

My Notation sheet is completely blank before running the code. After running the code it looks like this.

LeeLee17.xlsm
	A	B	C	D
1
2	A2	w
3	A4	abc
4	A6	kl
5	A9	z
6
Notation

If it is possible that any cells in column O in the 'Today' sheet contain numbers only, then my code would need a slight adjustment (but even so whole rows would not be copied) and I cannot generate the error you are reporting.