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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
 

jhndeere

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Then you can put a real example
 

jhndeere

New Member
Joined
Jun 28, 2005
Messages
17

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,838
Office Version
  1. 2013
Platform
  1. Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,157
Messages
5,527,131
Members
409,749
Latest member
esmarques

This Week's Hot Topics

Top