I'm using the following code that exists in this page: https://www.mrexcel.com/forum/excel...into-multiple-worksheets-based-column-11.html created by mirabeau.
The code works fine for distributing data from one main data sheet to several sheets after creating them using ColA criterion. What is needed to be modified in the code:
1. to check first if the data sheets already exist before creating them, and
2. if they exist only new data from the main data sheet should be appended to the last row of data in each sheet.
Applying these modifications will enrich the code instead of using it only for one time and then manually appending new data rows to their designated sheets.
Can I get some expert assistance with this request please?
very much appreciated.
Code:
[COLOR=#333333]Sub columntosheets() [/COLOR]
Const sname As String = "Sheet1" 'change to whatever starting sheet
Const s As String = "A" 'change to whatever criterion column
Dim d As Object, a, cc&
Dim p&, i&, rws&, cls&
Set d = CreateObject("scripting.dictionary")
With Sheets(sname)
rws = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
cls = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
cc = .Columns(s).Column
End With
For Each sh In Worksheets
d(sh.Name) = 1
Next sh
Application.ScreenUpdating = False
With Sheets.Add(after:=Sheets(sname))
Sheets(sname).Cells(1).Resize(rws, cls).Copy .Cells(1)
.Cells(1).Resize(rws, cls).Sort .Cells(cc), 2, Header:=xlYes
a = .Cells(cc).Resize(rws + 1, 1)
p = 2
For i = 2 To rws + 1
If a(i, 1) <> a(p, 1) Then
If d(a(p, 1)) <> 1 Then
Sheets.Add.Name = a(p, 1)
.Cells(1).Resize(, cls).Copy Cells(1)
.Cells(p, 1).Resize(i - p, cls).Copy Cells(2, 1)
End If
p = i
End If
Next i
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End With
Sheets(sname).Activate
[COLOR=#333333][COLOR=#333333]End Sub[/COLOR][/COLOR]
The code works fine for distributing data from one main data sheet to several sheets after creating them using ColA criterion. What is needed to be modified in the code:
1. to check first if the data sheets already exist before creating them, and
2. if they exist only new data from the main data sheet should be appended to the last row of data in each sheet.
Applying these modifications will enrich the code instead of using it only for one time and then manually appending new data rows to their designated sheets.
Can I get some expert assistance with this request please?
very much appreciated.