Populate data across multiple sheets

jhndeere

New Member
Joined
Jun 28, 2005
Messages
17
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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 ...

Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Assuming your correct data is in column A of Sheet(1)
Starting in A2

Try this script:

Code:
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
 
Upvote 0
Try this.
In column "E" will put a message with the result

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top