Option Explicit
Sub DistributeDataV2()
' hiker95, 09/09/2011
' http://www.mrexcel.com/forum/showthread.php?t=577018
Dim wM As Worksheet, ws As Worksheet
Dim MyS1 As Long, MyS2 As Long, MyS3 As Long, LR As Long, LUR As Long
Dim MyC1 As Long, MyC2 As Long, MyC3 As Long
Dim TR As Long, FR As Long, ER As Long, a As Long, NR As Long, N As String
Application.ScreenUpdating = False
Set wM = Worksheets("Master")
MyS1 = Application.Match("Section 1: Major Change", wM.Columns(1), 0)
MyS2 = Application.Match("Section 2: Minor Change", wM.Columns(1), 0)
MyS3 = Application.Match("Section 3: Supposed to Change but Didn't", wM.Columns(1), 0)
LR = wM.Cells(Rows.Count, 1).End(xlUp).Row
'********** Section 1: Major Change **********
TR = MyS1 + 2
FR = MyS1 + 3
ER = MyS2 - 4
For a = FR To ER Step 1
If Not Evaluate("ISREF(" & wM.Cells(a, 1) & "!A1)") Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = wM.Cells(a, 1)
N = wM.Cells(a, 1)
Set ws = Worksheets(N)
MyC1 = 0
On Error Resume Next
MyC1 = Application.Match("Section 1: Major Change", ws.Columns(1), 0)
On Error GoTo 0
If MyC1 = 0 Then
ws.Cells.Interior.ColorIndex = 2
wM.Range("A1:L7").Copy ws.Range("A1:L7")
wM.Range("A1:L1").Copy
With ws.Range("A1:L1")
.PasteSpecial Paste:=xlPasteColumnWidths
End With
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wM.Range("A" & a & ":M" & a).Copy ws.Range("A" & NR)
Else
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wM.Range("A" & a & ":M" & a).Copy ws.Range("A" & NR)
End If
Next a
'********** Section 2: Minor Change **********
TR = MyS2 + 2
FR = MyS2 + 3
ER = MyS3 - 4
For a = FR To ER Step 1
If Not Evaluate("ISREF(" & wM.Cells(a, 1) & "!A1)") Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = wM.Cells(a, 1)
N = wM.Cells(a, 1)
Set ws = Worksheets(N)
MyC1 = 0
On Error Resume Next
MyC1 = Application.Match("Section 1: Major Change", ws.Columns(1), 0)
On Error GoTo 0
If MyC1 = 0 Then
ws.Cells.Interior.ColorIndex = 2
wM.Range("A1:L7").Copy ws.Range("A1:L7")
wM.Range("A1:L1").Copy
With ws.Range("A1:L1")
.PasteSpecial Paste:=xlPasteColumnWidths
End With
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Range("A" & NR) = "Section Not Applicable"
With ws.Range("A" & NR & ":L" & NR)
.HorizontalAlignment = xlCenter
.MergeCells = True
.Interior.ColorIndex = 2
End With
End If
MyC2 = 0
On Error Resume Next
MyC2 = Application.Match("Section 2: Minor Change", ws.Columns(1), 0)
On Error GoTo 0
If MyC2 = 0 Then
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(2).Row
wM.Range("A" & MyS2 & ":L" & MyS2 + 2).Copy ws.Range("A" & NR)
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wM.Range("A" & a & ":M" & a).Copy ws.Range("A" & NR)
Else
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wM.Range("A" & a & ":M" & a).Copy ws.Range("A" & NR)
End If
Next a
'********** Check each worksheet for "Section 2: Minor Change" **********
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Instructions" And ws.Name <> "Master" Then
MyC2 = 0
On Error Resume Next
MyC2 = Application.Match("Section 2: Minor Change", ws.Columns(1), 0)
On Error GoTo 0
If MyC2 = 0 Then
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(2).Row
wM.Range("A" & MyS2 & ":L" & MyS2 + 2).Copy ws.Range("A" & NR)
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Range("A" & NR) = "Section Not Applicable"
With ws.Range("A" & NR & ":L" & NR)
.HorizontalAlignment = xlCenter
.MergeCells = True
.Interior.ColorIndex = 2
End With
End If
End If
Next ws
'********** Section 3: Supposed to Change but Didn't *********
TR = MyS3 + 2
FR = MyS3 + 3
ER = LR
For a = FR To ER Step 1
If Not Evaluate("ISREF(" & wM.Cells(a, 1) & "!A1)") Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = wM.Cells(a, 1)
N = wM.Cells(a, 1)
Set ws = Worksheets(N)
MyC1 = 0
On Error Resume Next
MyC1 = Application.Match("Section 1: Major Change", ws.Columns(1), 0)
On Error GoTo 0
If MyC1 = 0 Then
ws.Cells.Interior.ColorIndex = 2
wM.Range("A1:L7").Copy ws.Range("A1:L7")
wM.Range("A1:L1").Copy
With ws.Range("A1:L1")
.PasteSpecial Paste:=xlPasteColumnWidths
End With
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Range("A" & NR) = "Section Not Applicable"
With ws.Range("A" & NR & ":L" & NR)
.HorizontalAlignment = xlCenter
.MergeCells = True
.Interior.ColorIndex = 2
End With
End If
MyC2 = 0
On Error Resume Next
MyC2 = Application.Match("Section 2: Minor Change", ws.Columns(1), 0)
On Error GoTo 0
If MyC2 = 0 Then
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(2).Row
wM.Range("A" & MyS2 & ":L" & MyS2 + 2).Copy ws.Range("A" & NR)
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Range("A" & NR) = "Section Not Applicable"
With ws.Range("A" & NR & ":L" & NR)
.HorizontalAlignment = xlCenter
.MergeCells = True
.Interior.ColorIndex = 2
End With
End If
MyC3 = 0
On Error Resume Next
MyC3 = Application.Match("Section 3: Supposed to Change but Didn't", ws.Columns(1), 0)
On Error GoTo 0
If MyC3 = 0 Then
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(2).Row
wM.Range("A" & MyS3 & ":L" & MyS3 + 2).Copy ws.Range("A" & NR)
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wM.Range("A" & a & ":M" & a).Copy ws.Range("A" & NR)
Else
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
wM.Range("A" & a & ":M" & a).Copy ws.Range("A" & NR)
End If
Next a
'********** Check each worksheet for "Section 3: Supposed to Change but Didn't" **********
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Instructions" And ws.Name <> "Master" Then
MyC3 = 0
On Error Resume Next
MyC3 = Application.Match("Section 3: Supposed to Change but Didn't", ws.Columns(1), 0)
On Error GoTo 0
If MyC3 = 0 Then
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(2).Row
wM.Range("A" & MyS3 & ":L" & MyS3 + 2).Copy ws.Range("A" & NR)
NR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Range("A" & NR) = "Section Not Applicable"
With ws.Range("A" & NR & ":L" & NR)
.HorizontalAlignment = xlCenter
.MergeCells = True
.Interior.ColorIndex = 2
End With
End If
End If
Next ws
wM.Activate
Application.ScreenUpdating = True
End Sub