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

 

[Dryver14]

Are you wanting to select the name or write the name in the inputbox?

 

[bearcub]

Either way. I was thinking the user would rather chose the cell as opposed to typing it in. But, I would like to give them the flexibility of doing both.

 

[Dryver14]

This allows a selection.

I am not sure how to allow for both tbh

Sub DoNotPublish()


Dim res As String
Dim i As Long


    res = Application.InputBox(Prompt:="Select Members name who doesn't what their personal information published", Type:=8)
    i = Sheets("Do No Publish  - Contacts").Cells(Rows.Count, 2).End(xlUp) + 1
    Cells(i, 2) = res
End Sub

 

[Trebor76]

I have a do not publish sheet which will prevent their personnel information from being printed.

Could you not have another column called something like "Publish" that has a Y / N response which you filter by Y before printing? All the N's will be hidden and will not therefore print.

 

[Dryver14]

I kind of agree with Trebor on this less input for the user to mess up

 

[Trebor76]

I kind of agree with Trebor on this less input for the user to mess up

And no code to maintain

 

[bearcub]

I found this macro from 2009 that was posted on the message board. This one isn't working either. I can't see why the original code that posted is giving me a type mis match .

https://www.mrexcel.com/forum/excel-questions/339098-vba-send-data-input-box-another-worksheet.html

This was posted yesterday - which is generating a type mis match. Could it be the last line?

Sub DoNotPublish()
Dim res As String
Dim i As Long

    res = Application.InputBox(Prompt:="Select Members name who doesn't what their personal information published", Type:=8)
    i = Sheets("Do No Publish  - Contacts").Cells(Rows.Count, 2).End(xlUp) + 1
    Cells(i, 2) = res
End Sub

This is the one I found (which also doesn't work)

Sub myBuildList()
Dim lngLstRow&
Dim strMyIPBMsg$

   strMyIPBMsg = InputBox("Do  Not Publish Member:", "Get Member Name!")

     With Sheets("Do No Publish  - Contacts")
        lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row
       .Range("B" & lngLstRow).Value = strMyIPBMsg
     End With

Sheets("All SCC Ready to Import").Select
End Sub

For the life of me, I can't figure out why these two aren't working because on the surface it looks like they should, don't you think? I test the macro I found this morning by using a message box and I found that no text was being displayed (it made me wonder if I had set up the msgbox properly. I had it read msbox strMyIPBMsg. Or, did I do this incorrectly?

Thank you for your help,

Michael

 

[bearcub]

I just found the issue but I'm not quite sure how to solve this.

On the Do not Publish list I have formulas in all columns in the range other than column B - where I want them to them to add the name. The sheet Used range is 700 but the Column B used range is 24. The next entry should be on row 25. How would I find the used range in column B only?

It looks like the macros are looking at the entire used range on the sheet. I have formulas in Columns A, C-U which are looking up data for the member on the main sheet. If I l clear all the formulas from 25-700, is there a way that I could copy these formulas down to row 25 once there is a name entered?

Thank you again for your help and patience.

Michael

 

[My Aswer Is This]

Try this:

Sub DoNotPublish()
'Modified 3-16-18 5:30 PM EDT
Dim res As String
Dim Lastrow As Long
Lastrow = Sheets("Do No Publish  - Contacts").Cells(Rows.Count, "B").End(xlUp).Row + 1
res = Application.InputBox("Enter Members name who doesn't what their personal information published")
Sheets("Do No Publish  - Contacts").Cells((Lastrow), 2).Value = res
End Sub