Copy data entered in "Rows" in master worksheet to other worksheets

[mmcquinn2002]

Hi there,

I have a workbook containing 02 type of worksheets, i) Sheet1 named "Index" and ii) 10 worksheets named "Sheet2" to "Sheet11" containing data of individuals.

a. The "Index" Worksheet basically contains data in rows as follows:
Row1 - Name (B3), Address (C3) and Contact Number (D3).
Row2 - Name (B4), Address (C4) and Contact Number (D4).
Row3 - Name (B5), Address (C5) and Contact Number (D5).
Row4 - Name (B6), Address (C6) and Contact Number (D6).
Row5 - Name (B7), Address (C7) and Contact Number (D7).

b. The other worksheets contains details of the persons as follows:
Row1 - Name (B1),
Row2 - Address (B2)
Row3 - Contact (B3)

What i would like to do is to automatically update the data as follows:

1. In "Sheet2" - B1 data from "Index" B3, B2 from "Index" C3 and B3 from "Index" D3
2. In "Sheet 3" - B1 data from "Index" B4, B2 from "Index" C4 and B3 from "Index" D4
3. In "Sheet 4" - B1 data rom "Index" B5, B2 from "Index" C5 and B3 from "Index" D5.

Thanks in advance.

 

[Rosen]

Assuming I am understanding this correctly, for each row of data in the Index sheet, you want it to be in a column of data on subsequent sheets. Presumably there are 10 rows of data on the Index worksheet. Assuming I am understanding this correctly try this code

Public Sub TransferData()

    ' -----------------------------------------------------------------------------------------
    ' Defining Variables
    ' -----------------------------------------------------------------------------------------
    Dim Row As Long
    Dim LastRow As Long
    Dim SheetIndex As Long
    Dim Sheet As Worksheet
    Dim Index As Worksheet
    
    ' -----------------------------------------------------------------------------------------
    ' Assign Variables
    ' -----------------------------------------------------------------------------------------
    ' We are setting the field 'Row' to the first index row of data (based on your comments of
    ' Row1 containing Name in B3, the first row of data is 3
    Row = 3
    ' Based on the assumption that the last row is 10 rows down from the start
    LastRow = Row + 10
    ' Sheets are 2 through 11, so our starting value for SheetIndex is 2
    SheetIndex = 2
    ' Set a reference to our Index Worksheet
    Set Index = Sheets("Index")
        
    ' Now we cycle through each row in our index worksheet and transfer it to our sheets
    Do While Row < LastRow
        
        Set Sheet = Sheets("Sheet" & SheetIndex)
        Sheet.Range("B1").Value = Index.Range("B" & Row).Value
        Sheet.Range("B2").Value = Index.Range("C" & Row).Value
        Sheet.Range("B3").Value = Index.Range("D" & Row).Value
        
        SheetIndex = SheetIndex + 1
        Row = Row + 1
    Loop
End Sub

Please remember to backup your work before running new code.

Hope this helps!