Export data from TOC onto certain sheet of workbook

CalRich1023

New Member
Joined
Mar 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Mar 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

CalRich1023

New Member
Joined
Mar 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,063
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.

Rich (BB code):
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
 
Solution

CalRich1023

New Member
Joined
Mar 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

Rich (BB code):
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,063
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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 :ROFLMAO: )
 

CalRich1023

New Member
Joined
Mar 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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 :ROFLMAO: )
HAHAHA that was definitely not my intention!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,215
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top