How to Distribute data from a master tab to team tabs?

[DDubya]

Hello,
I'm looking to figure out how I can distribute from a Master tab to a multiple of other tabs within the same workbook.

I'm fairly new to Excel, self learning, but for some reason I cannot wrap my head around how to create this.

I appreciate any help I can get.

Thank you in advance.

 

[Takae]

Hope this helps.

Sub sample()
Dim Mws As Worksheet, tgws As Worksheet
Dim rng As Range, c
Set Mws = Sheets("M.A")

With Mws
    Set rng = .Range(.Range("C3"), .Cells(Rows.Count, 5).End(xlUp))
    For Each c In .Range(.Range("A3"), .Cells(Rows.Count, 1).End(xlUp))
        rng.Copy Sheets(c.Value).Range("A8")
    Next
End With
End Sub

 

[DDubya]

Hope this helps.

Sub sample()
Dim Mws As Worksheet, tgws As Worksheet
Dim rng As Range, c
Set Mws = Sheets("M.A")

With Mws
    Set rng = .Range(.Range("C3"), .Cells(Rows.Count, 5).End(xlUp))
    For Each c In .Range(.Range("A3"), .Cells(Rows.Count, 1).End(xlUp))
        rng.Copy Sheets(c.Value).Range("A8")
    Next
End With
End Sub

Hello Takae,

I appreciate your assistances here. The only thing is that it copied all the data into all the TM tabs, and I need them to distribute them, so that each TM has their own.
If that makes sense.

How do I take what you have and have it distribute each account individually to each TM?

Thank you again, much appreciated.

 

[Takae]

How do you want to distribute? If you have sheet name on column F(like columna A) on M.A sheet, it can distribute the data according to the sheet name.
For example, which sheet should the first data 1234567800/ST/45 should go?

 

[DDubya]

How do you want to distribute? If you have sheet name on column F(like columna A) on M.A sheet, it can distribute the data according to the sheet name.
For example, which sheet should the first data 1234567800/ST/45 should go?

Hi Takae,

So your VBA code added all accounts to all tabs. I was trying to get it to distribute the first account to the first tab, the second account to the second tab and so forth. When it comes to the end of the names (column A) then it was start at the top again, and continue to distribute the accounts out.

I appreciate all the help, thank you Takae for taking the time to assist me.

 

[DDubya]

Is it possible to have a check box next to those names to signal that YES they should distribute to that tab?

 

[Takae]

I still don't know what you want to do. How would you like to display it on the first TM1 sheet? It would be possible to put checkboxes, but what data should be displayed on which sheet when it is turned on?

Google 翻訳で開く

 

[DDubya]

Hi Takae,

I attached new images color coding to hopefully help my idea come across easier.

So each account will distribute to one of the TM's tab, then move down to distribute the next account to the next tab, and so forth.

I also added (Y/N) drop downs just to signify whether that TM should have account distributed to it.

Thank you Takae

 

[Takae]

Please try it.

Sub test()
Dim Mws As Worksheet
Dim x(), ws
Dim LR As Long, i As Long, j As Long, cnt As Long, flg As Boolean

Set Mws = Sheets("M.A.")
Application.ScreenUpdating = False

With Mws
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = 3 To LR
        If .Cells(i, 2).Value = "Yes" Then
           ReDim Preserve x(cnt)
           x(cnt) = .Cells(i, 2).Offset(0, -1).Value
           cnt = cnt + 1
        End If
    Next
    
    For i = 0 To UBound(x)
        For Each ws In Worksheets
            If ws.Name = x(i) Then
                flg = True
                Exit For
            End If
        Next
        If flg = False Then
            MsgBox x(i) & " Sheet Not Found"
            Exit Sub
        End If
        flg = False
    Next
    
    LR = .Cells(Rows.Count, 3).End(xlUp).Row
    For j = 0 To UBound(x)
        Sheets(x(j)).Range(Sheets(x(j)).Range("A8"), Sheets(x(j)).Range("A8").SpecialCells(xlLastCell).Offset(1, 0)).ClearContents
        For i = 3 + j To LR Step UBound(x) + 1
            .Range(.Cells(i, 3), .Cells(i, 5)).Copy Sheets(x(j)).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        Next
    Next

End With
Application.ScreenUpdating = True
MsgBox "Done"
End Sub

 

[DDubya]

Takae, Thank you, that works!

It keeps running the formula though, so it doesn't ever get to the msgbox Done. I have to hit Esc then when it stops all the account are where they need to be.

I apologize for not being clear enough the first time.

Thank you though for everything.