VBA to Loop threw a master sheets and copy data into a different sheet

[ConsGattuso]

Hello Everyone,
I have a master sheet in a work box that I want to use as a sign off sheet of sort. I have some 200 sub sheet so I'm looking for help with a macro to I can reference the sub sheets to the master sheet.
I want to enter data on the master sheet and have it copied over to the sub sheets. All of the sub sheet have same cell locations for Sig 1, Sig 2, Sig3 and Sig 4, just a they reside on different sheets.

Thank you in advanced for your help

 

[breynolds0431]

What about something like this?

Option Explicit

Sub FillSheets()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Data") '<- update to actual sheet name

'create index variables for signature cell locations; update indexes to suit
Dim colSig1 As Integer: colSig1 = 2 'column for signature one
Dim rowSig1 As Integer: rowSig1 = 5 'row for signature one
Dim colSig2 As Integer: colSig2 = 4 'column for signature two
Dim rowSig2 As Integer: rowSig2 = 5 'row for signature two
Dim colSig3 As Integer: colSig3 = 6 'column for signature three
Dim rowSig3 As Integer: rowSig3 = 5 'row for signature three
Dim colSig4 As Integer: colSig4 = 2 'column for signature four
Dim rowSig4 As Integer: rowSig4 = 5 'row for signature four

Application.ScreenUpdating = False

'Loop through all sheets as long as they are not ws
Dim sht As Worksheet 'variable for loop

For Each sht In ThisWorkbook.Sheets
    If Not sht.Name = ws.Name Then
        With sht 'mimics signature cells from ws in looped sheet
            .Cells(rowSig1, colSig1) = ws.Cells(rowSig1, colSig1)
            .Cells(rowSig2, colSig2) = ws.Cells(rowSig2, colSig2)
            .Cells(rowSig3, colSig3) = ws.Cells(rowSig3, colSig3)
            .Cells(rowSig4, colSig4) = ws.Cells(rowSig4, colSig4)
        End With
    End If
Next sht

Application.ScreenUpdating = True

End Sub

 

[ConsGattuso]

What about something like this?

Option Explicit

Sub FillSheets()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Data") '<- update to actual sheet name

'create index variables for signature cell locations; update indexes to suit
Dim colSig1 As Integer: colSig1 = 2 'column for signature one
Dim rowSig1 As Integer: rowSig1 = 5 'row for signature one
Dim colSig2 As Integer: colSig2 = 4 'column for signature two
Dim rowSig2 As Integer: rowSig2 = 5 'row for signature two
Dim colSig3 As Integer: colSig3 = 6 'column for signature three
Dim rowSig3 As Integer: rowSig3 = 5 'row for signature three
Dim colSig4 As Integer: colSig4 = 2 'column for signature four
Dim rowSig4 As Integer: rowSig4 = 5 'row for signature four

Application.ScreenUpdating = False

'Loop through all sheets as long as they are not ws
Dim sht As Worksheet 'variable for loop

For Each sht In ThisWorkbook.Sheets
    If Not sht.Name = ws.Name Then
        With sht 'mimics signature cells from ws in looped sheet
            .Cells(rowSig1, colSig1) = ws.Cells(rowSig1, colSig1)
            .Cells(rowSig2, colSig2) = ws.Cells(rowSig2, colSig2)
            .Cells(rowSig3, colSig3) = ws.Cells(rowSig3, colSig3)
            .Cells(rowSig4, colSig4) = ws.Cells(rowSig4, colSig4)
        End With
    End If
Next sht

Application.ScreenUpdating = True

End Sub

I type this in and it didn't work. I updated the work sheet name and adjusted the column and rows for the master sheet to grab the right data but it didn't do any thing....

 

[breynolds0431]

I type this in and it didn't work. I updated the work sheet name and adjusted the column and rows for the master sheet to grab the right data but it didn't do any thing....

Can you explain the signatures a bit more? Are they just text in the cells?

 

[ConsGattuso]

Can you explain the signaturres a bit more? Are they just text in the cells?

I was very clear in my description. The master sheet will have names under Sig1 thru Sig 4 for each row (ICD #). I want to be able to copy the text in Sig colunms over from the master sheet to each corresponding ICD# row to the ICD # sheet Sig 1- Sig 4 cells. The ICD# sheets are all set up the same so Sig 1 will appear in the same spot for all of the ICD sheet. the same for Sig2 and 3 and 4.
IT would go something like this....
1. Read cell A3 on master sheet to find name (ICD #) of the sheet to copy data to.
2. Find corresponding sheet named in step 1.
3. read Corresponding ICD# sig 1-4
4. Go to Sheet ICD# and Copy sig 1-4 into appropriate column
5. Go back to Master sheet and Index down from A3 to the next ICD # on master sheet
6. repeat step 2-5 until blank cell in Column A
7.
i hope this helps...

 

[breynolds0431]

I was very clear in my description. The master sheet will have names under Sig1 thru Sig 4 for each row (ICD #). I want to be able to copy the text in Sig colunms over from the master sheet to each corresponding ICD# row to the ICD # sheet Sig 1- Sig 4 cells. The ICD# sheets are all set up the same so Sig 1 will appear in the same spot for all of the ICD sheet. the same for Sig2 and 3 and 4.
IT would go something like this....
1. Read cell A3 on master sheet to find name (ICD #) of the sheet to copy data to.
2. Find corresponding sheet named in step 1.
3. read Corresponding ICD# sig 1-4
4. Go to Sheet ICD# and Copy sig 1-4 into appropriate column
5. Go back to Master sheet and Index down from A3 to the next ICD # on master sheet
6. repeat step 2-5 until blank cell in Column A
7.
i hope this helps...

You were not very clear in your description, but that's the last sentence I need to read any further from your question. Good luck

 

[ConsGattuso]

You were not very clear in your description, but that's the last sentence I need to read any further from your question. Good luck

I'm sorry if I offend you. I left the word "not" in in that first sentence. It should have read " I was not very clear in my description." Sometime my typing gets ahead of my thinking. Thanks you for the help you have already given me.