Hi,
I am using Excel 2010 and have the following code which is something I picked up in a forum and tweaked to my needs:
Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 17 'CHANGE THE COLUMN NUMBER AS PER YOUR NEED
Set ws = Sheets("WOs")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1" 'CHANGE THE TITLE ROW AS PER YOUR NEED
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
Sheets("RED").Tab.ColorIndex = 3
Sheets("AMBER").Tab.ColorIndex = 44
Sheets("GREEN").Tab.ColorIndex = 10
ws.AutoFilterMode = False
ws.Activate
End Sub
The code itself works fine, when running the macro I get three new tabs - Green, Amber, Red - with the relevant data being displayed.
The issue is that the Master Sheet instead of displaying all values, once the macro is run, displays the values of the last newly created sheet - meaning that if the last sheet created via macro is Green then the Master Sheet will only display Green values (or Red values or Amber values depending which is last).
The Master Sheet should remain unaltered showing all values (Red Amber Green or blank).
I have looked at another code I have which works fine where the Master Sheet is unaltered after Macro but that did not help me in this case (even when I copy paste the code and do the necessary changes - select a new input column - then I still get the same issue.)
Any help is appreciated.
R
I am using Excel 2010 and have the following code which is something I picked up in a forum and tweaked to my needs:
Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 17 'CHANGE THE COLUMN NUMBER AS PER YOUR NEED
Set ws = Sheets("WOs")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1" 'CHANGE THE TITLE ROW AS PER YOUR NEED
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
Sheets("RED").Tab.ColorIndex = 3
Sheets("AMBER").Tab.ColorIndex = 44
Sheets("GREEN").Tab.ColorIndex = 10
ws.AutoFilterMode = False
ws.Activate
End Sub
The code itself works fine, when running the macro I get three new tabs - Green, Amber, Red - with the relevant data being displayed.
The issue is that the Master Sheet instead of displaying all values, once the macro is run, displays the values of the last newly created sheet - meaning that if the last sheet created via macro is Green then the Master Sheet will only display Green values (or Red values or Amber values depending which is last).
The Master Sheet should remain unaltered showing all values (Red Amber Green or blank).
I have looked at another code I have which works fine where the Master Sheet is unaltered after Macro but that did not help me in this case (even when I copy paste the code and do the necessary changes - select a new input column - then I still get the same issue.)
Any help is appreciated.
R
