i am new to vba, so it will be great help if anyone of you could help me with this.
i have to increase the the sheet based on the name in the table .
and copy data from one sheet to multiple sheets.
i have format in one sheet(template), and data in another sheet(DATA). so i have to copy only selected data from (DATA) sheet to each individual sheet .
i have to increase the the sheet based on the name in the table .
and copy data from one sheet to multiple sheets.
i have format in one sheet(template), and data in another sheet(DATA). so i have to copy only selected data from (DATA) sheet to each individual sheet .
VBA Code:
Dim ws As Worksheet, Ct As Long, c As Range
Set ws = Worksheets("原紙")
Application.ScreenUpdating = False
'//担当者シートの作成
Dim myojiObject As Object
Set myojiObject = CreateObject("Scripting.Dictionary")
Set myojiObject = CreateObject("Scripting.Dictionary")
lastRow = Sheets("DATA").Range("O" & Sheets("DATA").Rows.Count).End(xlUp).Row
For i = 2 To lastRow
If myojiObject.Exists(Sheets("DATA").Range("O" & i).Value) = False Then
myojiObject.Add Sheets("DATA").Range("O" & i).Value, i
End If
Next i
Dim Keys() As Variant
'crete名字シート
Sheets.Add(After:=Sheets("人事データ")).Name = "名字"
Keys = myojiObject.Keys
With Sheets("名字")
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 1 To myojiObject.Count
.Range("A" & i + lastRow).Value = Keys(i - 1)
Next i
End With
For Each c In Sheets("名字").Range("A2:A20") 'row to the last end
If c.Value <> "" Then
ws.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value
Ct = Ct + 1
End If
Next c