Macro to add name to another sheet

[bearcub]

I have the following code that I need to modify.

Certain members do not want their address and phone published in our annual directories - they want to be excluded. I have a do not publish sheet which will prevent their personnel information from being printed. what I would like to do is have the user select a cell from the current sheet and then have that name pasted onto the do not publish sheet.

How would this be tweaked so that the name or reference to the name is copied and pasted onto the do not publish sheet?

Sub DoNotPublish()
Dim res As String
res = Application.InputBox("Enter Members name who doesn't what their personal information published")
Sheets("Do No Publish  - Contacts").Cells(Rows.Count, 2).End(xlUp).Value = res
End Sub

I realize that their isn't a reference to column B on the "Do Not Publish - Contacts sheet". I ran the code as is, pointed to a cell on the active sheet and nothing happened.

Thank you for your help,

Michael

 

[bearcub]

Thank you that works.

Oh, by the way, there a way to modify the code so that if they wanted to select a range of members to add they can do that? As it stands, it is one member at time. If it is too much trouble this is fine, I'm happy.

Thank you all for your help,

Michael

 

[Fluff]

How about

Sub DoNotPublish()
Dim res As Range
Set res = Application.InputBox("Select range of members", Type:=8)
res.Copy Sheets("Do No Publish  - Contacts").Cells(Rows.Count, 2).End(xlUp).Offset(1)
End Sub

 

[bearcub]

I tried it but it seems to be copying data that is 20 rows before the selection:

I ran this macro twice but instead of copying:

Kris Ackerman
Julie Aiello
Susan Albritton
Robin Alderson
Vanessa Alexander

it copied

Curtis Washington
Gretchen Lipow
Gretchen Lipow
Val Muchowski
Michelle Washington
Cynthia Erickson
Karen Fleming
Emilie King
Channel Lewis
Sheila Bell

These are all in row order but Curtis's name appears 20 rows before Kris.

What does the offset(1) do - does it go down a row?

Thank you for your help,

Michael

 

[My Aswer Is This]

My question would be how do you decide which members get added to this list.
When you look at a name how do you decide which ones to add to the "Do not publish list"

Because we could just have the script do the same thought process you are using in your head and have the script to the thinking for you.

 

[Fluff]

When the input box comes up, use the cursor to highlight the cells you want to copy & click ok.

 

[bearcub]

this is just what I did - select either a cell or a range of cell references in the input box. What is strange is the the multiple cells that were copied started coping names that appeared 20 rows before the name started. In other words, if I select A40:A45 it copied cells A20:A25 which I wouldn't think would happen but it did.

Michael

 

[Fluff]

It shouldn't do that & doesn't for me.
I'll have another look at it over the weekend, its pub time now

 

[bearcub]

You're right, it shouldn't do that.

I'll test it again later. Maybe I had something in there that shouldn't be.

Thank you again.

Enjoy the pub!

Michael