Copying from a pick list- creating duplicates on the final output list

[Pat_The_Bat]

I have some code working right now where there are 2 master lists of documents that may be needed in a file. The user goes through and puts an "x" next to the documents they want.
The code is doing what it is supposed to,... it copies any document with an x next to it, and moves it to the output page, but then when you run the macro again, it starts adding the same documents to the list.
Once a documents was swept in a previous run, it should no longer get added to the list.
The user may hit the command button to run the sub multiple/many times, so it can't keep adding duplicates every time.

This is what I'm running now:

'Then add any documents from Master to the Doc Checklist


With Sheets("Master").Range("B2:B100").SpecialCells(xlConstants)
.Offset(, 1).Copy Sheets("Doc Checklist").Range("C2")
End With
On Error Resume Next
Sheets("Doc Checklist").Range("C2:C100").SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
'Then this section looks for any other documents on the Doc Request sheet and adds them to the the Doc Checklist


With Sheets("Doc Request").Range("B17:B100").SpecialCells(xlConstants)
.Offset(, -1).Copy Sheets("Doc Checklist").Range("C2").End(xlDown)
'The line above this needs to reference range = the next available cell in the column C
End With
On Error Resume Next
Sheets("Doc Checklist").Range("C2:C100").SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0

 

[Pat_The_Bat]

I added Debug.print srchhk and Debug.Print cprange so I could get a better understanding of what's happening there

'based on column C we need to find row range to search for mark in column Z. We will return address for copy
srcchk = .Range("B2:B100").SpecialCells(xlConstants).Offset(, 24).Address
MsgBox srcchk
Debug.Print srcchk

The result in the immdediate window is $Z$4 (because I have an "x" in B4. That is the only "x" I have in Column B right now)

Then,

'based on address from srcchk we determine address range of blank cells
On Error GoTo err_msg
cprange = .Range(srcchk).SpecialCells(xlBlanks).Address
Debug.Print cprange

The result in the immdediate window is
$A$1:$B$3,$B$5:$B$9

Cells A1:B3 are blank
Cells A4:A9 have document names in them
Cells A9:B100 are blank

[TABLE="width: 199"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](Blank)
(Blank)
(Blank)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017 W2[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]2016 W2 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paystubs [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gift Letter [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Identification[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bank Statements [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




The rows that should get copied in Column A, should be any row where there is an X in Column B, but not an X in Column Z.

Right now cprange is returning a range of A1:B3 and B5:B9.


Given the table above, cprange should only return one value and that would be A4. (because B4 has an "x", and Z4 has no "x")

 

[nardagus]

Problem starts when there is only one X in column B.
Fast fix for it would be: put x in B2 and in row you want to copy.
Having x in Z2 as well.
Proper fix... I need to think as I'm not sure.

 

[Pat_The_Bat]

the part that I don't understand is
if cprange is set to = .Range(srcchk).SpecialCells(xlBlanks).Address

then that is essentially looking at column Z for any cells in column Z that have a blank cell. How could the answer to that be any address that doesn't start with Z
The correct answer should be Z4

Srcchk only returns one cell in it's range, which Z4.
Then cprange checks Z4 to see if it is blank or not.

headscratcher

 

[Pat_The_Bat]

I put an "x" in B2 and Z2 and you were correct. The code works. I can use it this way, but I'd love to understand why it doesn't work when there is only 1 x in the column, and then it does work when there is no "x" in the column. Any way to clarify that? Thanks for all your help!

 

[nardagus]

To answer that I believe there are smarter heads than mine.
When srcchk is at least two cell range you lock a macro in this range. That's why it works. However when a range has only one cell it behaves more or less like CTRL+F. It looks everywhere in a sheet.
How to overcome this in a nice way? No idea. As someone wrote in his signature:
"The older I get the more I'm aware that I know less, and the more I should learn."

 

[Pat_The_Bat]

To answer that I believe there are smarter heads than mine.
When srcchk is at least two cell range you lock a macro in this range. That's why it works. However when a range has only one cell it behaves more or less like CTRL+F. It looks everywhere in a sheet.
How to overcome this in a nice way? No idea. As someone wrote in his signature:
"The older I get the more I'm aware that I know less, and the more I should learn."

Roger that. I'm good with it. I've added an "x" to both columns and hid that row from users. Works great! Really appreciate all the help!!!

 

[nardagus]

Glad I could help.