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?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

CalRich1023

New Member
Joined
Mar 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I've tried this, but get an error. Could somebody correct me in my errors?

VBA Code:
Sub Export_Locations()

Dim Ws As Worksheet

    For Each Ws In Sheets

        If ActiveSheet.Range("B2:B").Value = Ws.Name Then
        
            ActiveSheet.Range("A2:A").CurrentRegion.Copy Destination:=Ws.Range("B4")
        
        End If
    
    Next


End Sub
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,062
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
The 2 ranges below don't have a row number for the end of the range so are invalid references
VBA Code:
Range("B2:B")
VBA Code:
Range("A2:A")
You would need something like
VBA Code:
Range("B2:B" & Range("B" & Rows.count).End(xlUp).Row)

and you can't test multiple cells against a single string the way that you are trying, you would normally loop through the range.
 

CalRich1023

New Member
Joined
Mar 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
The 2 ranges below don't have a row number for the end of the range so are invalid references
VBA Code:
Range("B2:B")
VBA Code:
Range("A2:A")
You would need something like
VBA Code:
Range("B2:B" & Range("B" & Rows.count).End(xlUp).Row)

and you can't test multiple cells against a single string the way that you are trying, you would normally loop through the range.
What is the correct way to do this? I thought that the "For Each/Next" was the looping factor?
 

MARK858

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

ADVERTISEMENT

Looking again at your explanation then it looks like you want it to transfer when you type in column A, is that correct?
If it is then you need a worksheet change code which is totally different to what you have posted.
 

CalRich1023

New Member
Joined
Mar 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Looking again at your explanation then it looks like you want it to transfer when you type in column A, is that correct?
If it is then you need a worksheet change code which is totally different to what you have posted.
Not necessarily transfer, but just copy that data to the corresponding sheet.

It doesn't have to be automated, but I suppose that would make things more streamlined. I'm not at all familiar with worksheet change codes, so I don't even know where to begin with that.
 

MARK858

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

ADVERTISEMENT

worksheet change codes, so I don't even know where to begin with that.
I'll put something together for you to try but it will be a little while as I am just about to get something to eat
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,062
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Possibly something like the code below. The code goes in the TOC sheets Worksheet module (right click the TOC sheets tab, click view code and paste it the window that appears).
The Text is column B has to exactly match the destination sheets name or it will error.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing And Target.Offset(, 1) <> "" Then
        Application.EnableEvents = False
        Sheets(Target.Offset(, 1).Value).Range("B4").Value = Target.Value
    End If
    Application.EnableEvents = True
End Sub
 

CalRich1023

New Member
Joined
Mar 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Possibly something like the code below. The code goes in the TOC sheets Worksheet module (right click the TOC sheets tab, click view code and paste it the window that appears).
The Text is column B has to exactly match the destination sheets name or it will error.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing And Target.Offset(, 1) <> "" Then
        Application.EnableEvents = False
        Sheets(Target.Offset(, 1).Value).Range("B4").Value = Target.Value
    End If
    Application.EnableEvents = True
End Sub
This works if I type information into the cell in column A. Is there a reason that it isn't working when I enter the data in column A using the 'form' feature?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,115
Messages
5,640,199
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