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]

I've tried this, but get an error. Could somebody correct me in my errors?

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]

The 2 ranges below don't have a row number for the end of the range so are invalid references

Range("B2:B")

Range("A2:A")

You would need something like

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]

The 2 ranges below don't have a row number for the end of the range so are invalid references

Range("B2:B")

Range("A2:A")

You would need something like

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]

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]

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]

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]

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.

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]

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.

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?

 

[MARK858]

using the 'form' feature?

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