Export data from TOC onto certain sheet of workbook

[CalRich1023]

Hello,

I have a Table of Contents page in my workbook (formatted as a table). Column B is the list of all the sheet names in the workbook (with hyperlinks to each sheet). Column A is blank, but I would like to be able to manually enter data into it, and have that data exported to cell "B4" of the corresponding worksheet.

For example:
Table of Contents Cell "B2" contains the sheetname (hyperlink) "Sheet1".
I enter "500" into Cell "A2".
I would like the contents from "A2" ("500" in this example) to be copied onto worksheet "Sheet1"_Cell "B4" of the workbook.

Is this possible?

 

[CalRich1023]

Explain exactly what you mean by "the form feature". It won't work if the data in column A is changing by formula.

The Table of Contents is formatted as a table, containing all of the sheet names. So to easily navigate/input data, I like to use the 'form' feature. Similar to a userform I think? (I'm not familiar with userforms either haha).

If you go to customize your ribbon/quick access toolbar, you can select "commands not in the ribbon" --> "Form...". This opens a form to be able to easily enter/navigate data in the table (ONLY A TABLE) that you're working with.

So with the form open, I enter what I'm looking for in column B's input box (in this case the sheet name). Then I can enter the data I want in column A's Input box.

When I do this with your code, the data is input into the Table like I want (using the form) but it is not copied over to the sheets. It doesn't make sense to me. I figure, if there's data there, it should be copied, right?

 

[CalRich1023]

The Table of Contents is formatted as a table, containing all of the sheet names. So to easily navigate/input data, I like to use the 'form' feature. Similar to a userform I think? (I'm not familiar with userforms either haha).

If you go to customize your ribbon/quick access toolbar, you can select "commands not in the ribbon" --> "Form...". This opens a form to be able to easily enter/navigate data in the table (ONLY A TABLE) that you're working with.

So with the form open, I enter what I'm looking for in column B's input box (in this case the sheet name). Then I can enter the data I want in column A's Input box.

When I do this with your code, the data is input into the Table like I want (using the form) but it is not copied over to the sheets. It doesn't make sense to me. I figure, if there's data there, it should be copied, right?

Another problem I just encountered is that when I add new sheets to the workbook and refresh my TOC, it gives me an error in the first line of your code.

 

[MARK858]

Ok, I haven't been able to find an Event that is triggered by the entry via the form so try a looping code.
Place the below in a regular module, change where the red TOC is to your Table of Contents sheet name and try running the code below.

Sub TocCopy()
    Dim MyCell As Range
    Application.ScreenUpdating = False
    For Each MyCell In Sheets("TOC").Range("A2:A" & Sheets("TOC").Range("A" & Rows.Count).End(xlUp).Row)
        If MyCell.Value <> "" And MyCell.Offset(, 1) <> "" Then Sheets(MyCell.Offset(, 1).Value).Range("B4").Value = MyCell.Value
    Next
    Application.ScreenUpdating = True
End Sub

 

[CalRich1023]

Ok, I haven't been able to find an Event that is triggered by the entry via the form so try a looping code.
Place the below in a regular module, change where the red TOC is to your Table of Contents sheet name and try running the code below.

Sub TocCopy()
    Dim MyCell As Range
    Application.ScreenUpdating = False
    For Each MyCell In Sheets("TOC").Range("A2:A" & Sheets("TOC").Range("A" & Rows.Count).End(xlUp).Row)
        If MyCell.Value <> "" And MyCell.Offset(, 1) <> "" Then Sheets(MyCell.Offset(, 1).Value).Range("B4").Value = MyCell.Value
    Next
    Application.ScreenUpdating = True
End Sub

THIS is exactly what I was looking for!! THANK YOU SO MUCH!!
I actually played around with a userform while I was waiting for your response and got that to work for me also (my first userform!) but I really like this method better!

Thanks again so much! This is perfect!

 

[MARK858]

You're welcome (even though you have given me a headache now of investigating to see if I can find a way of trapping the action by the form )

 

[CalRich1023]

You're welcome (even though you have given me a headache now of investigating to see if I can find a way of trapping the action by the form )

HAHAHA that was definitely not my intention!

 

[MARK858]

HAHAHA that was definitely not my intention!

you never stop learning.... hopefully