Populate data across multiple sheets

[jhndeere]

I have a column of data (Column A) that has the correct information. This information from one cell needs to get corrected on each sheet (there is 1 cell of corrected data for each sheet, and the input location on each sheet is the same).

Example:

Master value in A2 needs to go to cell B6 on Sheet 2.
Master value in A3 needs to go to cell B6 on Sheet 3.
Master value in A4 needs to go to cell B6 on Sheet 4.
etc...

Is there a way to easily do this?

Thanks
-J

 

[DanteAmor]

With formulas:
In sheet2 on cell B6 put the formula:
=Master!A2

In sheet3 on cell B6 put the formula:
=Master!A3

With Macro. The sheet names must be sheet2, sheet3, sheet4 ... then the value of A2 (row 2) goes on sheet sheet2, A3 on sheet3, A4 on sheet4 ...

Sub Populate_Data()
    Dim sh As Worksheet, s As Worksheet, wData As Range, wName As String, exists As Boolean
    
    Set sh = Sheets("Master")
    For Each wData In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
        wName = "Sheet" & wData.Row
        exists = False
        For Each s In Sheets
            If LCase(s.Name) = LCase(wName) Then exists = True: Exit For
        Next
        If exists = True Then
            Sheets(wName).Range("B6").Value = wData.Value
        Else
            MsgBox "Sheet " & wName & " does not exist"
            Exit For
        End If
    Next
    MsgBox "End"
End Sub

 

[jhndeere]

Thank you. Unfortunately the sheets are not named Sheet2, Sheet3, etc... I can create a column though with the sheet names if that would help.

-J

 

[DanteAmor]

Then you can put a real example

 

[jhndeere]

The workbook is an old inventory management system for a company, so there is 2,137 sheets in the workbook. There is no pattern to the way they were named. They are all setup the same (information all in the same cell on each sheet). On the master sheet I used column C to list every single sheet name in the order they are in the wookbook. Examples of sheet names are:

205000067C
205000068B
205000070A
205000070A2
500450004A
500450005A

Thanks
-J

 

[My Aswer Is This]

Assuming your correct data is in column A of Sheet(1)
Starting in A2

Try this script:

Sub Correcting_Data()
'Modified 3/18/2019 11:28:18 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
    For i = 2 To Sheets.Count
        Sheets(i).Cells(6, "B").Value = Sheets(1).Cells(i, 1).Value
    Next
Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

Try this.
In column "E" will put a message with the result

Sub Populate_Data()
    Dim sh As Worksheet, s As Worksheet, wData As Range, wName As String, exists As Boolean
    
    Set sh = Sheets("Master")
    For Each wData In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp))
        wName = wData.Offset(0, 2)
        exists = False
        For Each s In Sheets
            If LCase(s.Name) = LCase(wName) Then exists = True: Exit For
        Next
        If exists = True Then
            Sheets(wName).Range("B6").Value = wData.Value
            wData.Offset(0, 4).Value = "Updated data"
        Else
            wData.Offset(0, 4).Value = "Sheet does not exists"
        End If
    Next
    MsgBox "End"
End Sub