Find Specific Rows in Range and Create New Tab

[excelnoob]

Hello all,
I have a sorting issue I hope someone can assist with. I have a “roll up” sheet that combines data from numerous tabs. All the tabs have the exact same headers as well as the roll up. The header range is A18:Z18 and the data starts directly below in row 19. The header in column E (called Job#) is what I need to sort by. There are numerous job numbers and are not in any particular order.

I am in need of a macro that will search the Job# column, find all the rowswith that specific job number, insert a new sheet, rename the sheet the "Job" and job number, and then populate the headers and the cell references (not copy, since I need to keep the references to the roll up). It would also be good if the macro could sort by the names in column A, but is not necessary.

For example, in the “roll up” there are 100 rows with 20 unique job numbers (column E). Job number 5555 shows up 30 times in the table with no real pattern. I would like the macro to find all 30 rows for job 5555, then open a new tab, rename the tab “Job 5555” and populate the tab with the all the headers (and in row 18 as well) and the cell references from the master directly below the headers. Once complete, it moves on to the next jobnumber and repeats until all unique job numbers have their own sheets.

Any feedback would be fantastic. Thank you in advance.

 

[Claus Busch]

Hi,

Am Thu, 24 Oct 2013 11:35:53 -0700 (PDT) schrieb excelnoob:

I have a sorting issue I hope someone can assist with. I have a ?roll up? sheet that combines data from numerous tabs. All the tabs have the exact same headers as well as the roll up. The header range is A18:Z18 and the data starts directly below in row 19. The header in column E (called Job#) is what I need to sort by. There are numerous job numbers and are not in any particular order.

I am in need of a macro that will search the Job# column, find all the rows with that specific job number, insert a new sheet, rename the sheet the "Job" and job number, and then populate the headers and the cell references (not copy, since I need to keep the references to the roll up). It would also be good if the macro could sort by the names in column A, but is not necessary.

For example, in the ?roll up? there are 100 rows with 20 unique job numbers (column E). Job number 5555 shows up 30 times in the table with no real pattern. I would like the macro to find all 30 rows for job 5555, then open a new tab, rename the tab ?Job 5555? and populate the tab with the all the headers (and in row 18 as well) and the cell references from the master directly below the headers. Once complete, it moves on to the next job number and repeats until all unique job numbers have their own sheets.

try:

Sub Test()
Dim LRow As Long
Dim LrowT As Long
Dim rngC As Range
Dim varFilter() As Variant
Dim i As Integer

Application.ScreenUpdating = False
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Temp"
With Sheets("RollUp")
LRow = .Cells(Rows.Count, "E").End(xlUp).Row
.Range("E18:E" & LRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True

LrowT = Sheets("Temp").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LrowT
ReDim Preserve varFilter(LrowT - 2)
varFilter(i - 2) = Sheets("Temp").Cells(i, 1)
Next

With .Range("A18:Z" & LRow)
For i = LBound(varFilter) To UBound(varFilter)
.AutoFilter field:=5, Criteria1:=varFilter(i)
.Copy
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Job " & varFilter(i)
[A18].Select
ActiveSheet.Paste link:=True
ActiveSheet.UsedRange.Sort key1:=Range("A18"), _
Order1:=xlAscending, Header:=xlYes
Next
End With
..AutoFilterMode = False
End With
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[excelnoob]

Hello Claus,
Thank you for such a quick response. The macro works as it should for the most part, however after using it, I just realized I may need to take it a slightly different direction.
Instead of having it create a new tab for each sort, I need to have it pull from a template called "JobTemp". I start the headers on row 18 as the section above is used for additional information for each job.

Would it be possible to use JobTemp as the start sheet for all the "Job xxxx" sheets?

Also, the formatting seems to be off. It wants to bring in the "date" format for almost all the columns. I have tried changing the formatting of the source data, but to no avail. Any feedback helps. Thank you again.

 

[Claus Busch]

Hi,

Am Thu, 24 Oct 2013 14:11:54 -0700 (PDT) schrieb excelnoob:

Thank you for such a quick response. The macro works as it should for the most part, however after using it, I just realized I may need to take it a slightly different direction.
Instead of having it create a new tab for each sort, I need to have it pull from a template called "JobTemp". I start the headers on row 18 as the section above is used for additional information for each job.

Would it be possible to use JobTemp as the start sheet for all the "Job xxxx" sheets?

Also, the formatting seems to be off. It wants to bring in the "date" format for almost all the columns. I have tried changing the formatting of the source data, but to no avail. Any feedback helps. Thank you again.

try:

Sub Test()
Dim LRow As Long
Dim LrowT As Long
Dim rngC As Range
Dim varFilter() As Variant
Dim i As Integer

Application.ScreenUpdating = False
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Temp"
With Sheets("JobTemp")
LRow = .Cells(Rows.Count, "E").End(xlUp).Row
.Range("E18:E" & LRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True

LrowT = Sheets("Temp").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LrowT
ReDim Preserve varFilter(LrowT - 2)
varFilter(i - 2) = Sheets("Temp").Cells(i, 1)
Next

With .Range("A18:Z" & LRow)
For i = LBound(varFilter) To UBound(varFilter)
.AutoFilter field:=5, Criteria1:=varFilter(i)
.Copy
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Job " & varFilter(i)
[A18].Select
ActiveSheet.Paste link:=True
ActiveSheet.Range("A18").PasteSpecial xlPasteFormats
ActiveSheet.UsedRange.Sort key1:=Range("A18"), _
Order1:=xlAscending, Header:=xlYes
Next
End With
..AutoFilterMode = False
End With
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Regards
Claus B.

 

[excelnoob]

Hello Claus,
I think it is getting closer, however I am getting an error with the 12th line down

.Range("E18:E" & LRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True

For some reason it does not like this code now. My JobTemp tab does have some data and formulas above the header (rows 1-18 with the header in row 18). However when I clear all the info and headers from the JobTemp tab, it seems to work, however I get another error with this line of code.

For i = LBound(varFilter) To UBound(varFilter)

Ultimately, I would like to have whatever I need in rows 1-18 on the JobTemp tab as I know this will change in the future.

Thank you again for your support.

 

[Claus Busch]

Hi,

Am Fri, 25 Oct 2013 07:40:00 -0700 (PDT) schrieb excelnoob:

.Range("E18:E" & LRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True

For some reason it does not like this code now. My JobTemp tab does have some data and formulas above the header (rows 1-18 with the header in row 18). However when I clear all the info and headers from the JobTemp tab, it seems to work, however I get another error with this line of code.

my suggestions are always tested and are working. Did you change
anything in the code?
First a new sheet "Temp" will be created and all job numbers will be
copied as unique numbers to this sheet. Only if this sheet exists the
filter array varFilter can be filled. At the end of the code this sheet
"Temp" will be deleted.


Regards
Claus B.

 

[excelnoob]

I think I know where the issue is (besides with me). Per the changes in the new code that you provided, I had changed the original code

With Sheets("RollUp")
To
With Sheets("JobTemp")

I believe the code does not know to look in the RollUp tab for the data. I am need of the code to look to the RollUp tab for the data and use the JobTemp tab to populate all the other tabs based on the unique Job Number.

Macros are a fairly new experience for me so I apologize for my lack of understanding the language. Any advice/tips is always welcome. Thank you again

 

[Claus Busch]

Hi,

Am Fri, 25 Oct 2013 08:55:57 -0700 (PDT) schrieb excelnoob:

With Sheets("RollUp")
To
With Sheets("JobTemp")

I believe the code does not know to look in the RollUp tab for the data. I am need of the code to look to the RollUp tab for the data and use the JobTemp tab to populate all the other tabs based on the unique Job Number.

why do you want the JobTemp tab? Or do you want to name the new sheets
with JobTemp 5555 and so on?


Regards
Claus B.

 

[excelnoob]

Hi Claus,
Sorry for my delay. I did not have access to a computer this weekend.
I am thinking of using the JobTemp tab as the template (I should call it JobTemplate) that will already be in the workbook. In JobTemplate, the cells in rows 1-18 will be preformatted with headers, text, etc..., for printing purposes. There will be one JobTemplate tab in workbook. When the macro runs, it will copy the JobTemplate tab, populate the data starting in row 19 (directly below the header) and then rename. It will do this for all unique values from Column E.
In the current macro, new tabs are being populated without the preexisting information needed in rows 1-18. This was something I realized after I ranthe macro.

Again, I welcome any feedback and thank you for your assistance.

 

[Claus Busch]

Hi,

Am Mon, 28 Oct 2013 07:48:30 -0700 (PDT) schrieb excelnoob:

I am thinking of using the JobTemp tab as the template (I should call it JobTemplate) that will already be in the workbook. In JobTemplate, the cells in rows 1-18 will be preformatted with headers, text, etc..., for printing purposes. There will be one JobTemplate tab in workbook. When the macro runs, it will copy the JobTemplate tab, populate the data starting in row 19 (directly below the header) and then rename. It will do this for all unique values from Column E.
In the current macro, new tabs are being populated without the preexisting information needed in rows 1-18. This was something I realized after I ran the macro.

ok, now I am understanding the problem.
Try:

Sub Test()
Dim LRow As Long
Dim LrowT As Long
Dim rngC As Range
Dim varFilter() As Variant
Dim i As Integer

Application.ScreenUpdating = False
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Temp"
With Sheets("RollUp")
LRow = .Cells(Rows.Count, "E").End(xlUp).Row
.Range("E18:E" & LRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True

LrowT = Sheets("Temp").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LrowT
ReDim Preserve varFilter(LrowT - 2)
varFilter(i - 2) = Sheets("Temp").Cells(i, 1)
Next

With .Range("A18:Z" & LRow)
For i = LBound(varFilter) To UBound(varFilter)
.AutoFilter field:=5, Criteria1:=varFilter(i)
Sheets("RollUp").Range("A19:Z" & LRow).Copy
Sheets("JobTemp").Activate
Range("A19").Select
ActiveSheet.Paste link:=True
Sheets("JobTemp").Copy After:=Sheets(Sheets.Count)
Sheets("JobTemp (2)").Name = "Job " & varFilter(i)
ActiveSheet.UsedRange.Sort key1:=Range("A18"), _
Order1:=xlAscending, Header:=xlYes
Next
End With
..AutoFilterMode = False
End With
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[excelnoob]

Hi Claus
It seems to be working with the exception that when it populates the JobTemp (JobTemplate) tab, it starts directly below the first row of text, ratherthan the header which is in Row 19. I have text and some formulas in rows1-18 which are needed for printing purposes.

I search the code, looking for where this could be, but my novice eyes cannot interpret very well. Overall, I just need it to start copying the links in row 19 on the JobTemp tab.

Also, I layered in the Paste Format (row 2 below)

ActiveSheet.Paste link:=True
ActiveSheet.Range("A18").PasteSpecial xlPasteFormats

However, my formats are still copying and pasting incorrectly. For some reason the date format keeps popping up where it should not. Is there a way towrite the code to reference a specific range on the rollup tab for all links and use this all the way throughout?

Again, thank you for your time and expertise. I am learning a lot!

 

[Claus Busch]

Hi again,

Am Wed, 30 Oct 2013 12:23:38 -0700 (PDT) schrieb excelnoob:

It seems to be working with the exception that when it populates the JobTemp (JobTemplate) tab, it starts directly below the first row of text, rather than the header which is in Row 19. I have text and some formulas in rows 1-18 which are needed for printing purposes.

no, the macro fills JobTemp under the header. But in the copied sheets
was an error in sorting.
Try:

Sub Test()
Dim LRow As Long
Dim LrowT As Long
Dim rngC As Range
Dim varFilter() As Variant
Dim i As Integer

Application.ScreenUpdating = False
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Temp"
With Sheets("RollUp")
LRow = .Cells(Rows.Count, "E").End(xlUp).Row
.Range("E18:E" & LRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True

LrowT = Sheets("Temp").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LrowT
ReDim Preserve varFilter(LrowT - 2)
varFilter(i - 2) = Sheets("Temp").Cells(i, 1)
Next

With .Range("A18:Z" & LRow)
For i = LBound(varFilter) To UBound(varFilter)
.AutoFilter field:=5, Criteria1:=varFilter(i)
Sheets("RollUp").Range("A19:Z" & LRow).Copy
Sheets("JobTemp").Activate
Range("A19").Select
ActiveSheet.Paste link:=True
Sheets("JobTemp").Copy After:=Sheets(Sheets.Count)
Sheets("JobTemp (2)").Name = "Job " & varFilter(i)
With ActiveSheet
.Range("A18:Z" & .UsedRange.Rows.Count) _
.Sort key1:=Range("A18"), Order1:=xlAscending, _
Header:=xlYes
End With
Next
End With
..AutoFilterMode = False
End With
Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[excelnoob]

It works! The only thing I am having an issue with is the formatting. for some reason, all the numbers want to format to the date format and I can't figure out why. I have changed the formats on all my source documents as well as the JobTemp tab but it always goes back to dates. My first instinct would be to have a "master" format range on the Rollup tab say, A1:Z1. Here I would create my formats and if I have to change or add later on, it wouldthen be easy to format the range. Not sure if this is the right approach,but just a thought. Any feedback helps. Thank you again.

 

[Claus Busch]

Hi,

Am Wed, 30 Oct 2013 14:23:09 -0700 (PDT) schrieb excelnoob:

It works! The only thing I am having an issue with is the formatting. for some reason, all the numbers want to format to the date format and I can't figure out why. I have changed the formats on all my source documents as well as the JobTemp tab but it always goes back to dates. My first instinct would be to have a "master" format range on the Rollup tab say, A1:Z1. Here I would create my formats and if I have to change or add later on, it would then be easy to format the range. Not sure if this is the right approach, but just a thought. Any feedback helps. Thank you again.

format your cells in RollUp as expected and copy the format to JobTemp
and change that part of the code:

With .Range("A18:Z" & LRow)
For i = LBound(varFilter) To UBound(varFilter)
.AutoFilter field:=5, Criteria1:=varFilter(i)
Sheets("RollUp").Range("A19:Z" & LRow).Copy
Sheets("JobTemp").Activate
Range("A19").Select
ActiveSheet.Paste link:=True
ActiveSheet.Range("A19").PasteSpecial xlPasteFormats
Sheets("JobTemp").Copy After:=Sheets(Sheets.Count)
Sheets("JobTemp (2)").Name = "Job " & varFilter(i)
With ActiveSheet
.Range("A18:Z" & .UsedRange.Rows.Count) _
.Sort key1:=Range("A18"), Order1:=xlAscending, _
Header:=xlYes
End With
Next
End With

Regards
Claus B.

 

[excelnoob]

Hi Claus,
I was able to get the formatting to work appropriately. Thank you! However,I noticed when it populates the Job tabs from the JobTemp tab, it seems that the JobTemp tab is not clearing out all the way. The first sheet looks good, however when it gets to the second sheet, it still has some information from the previous run through. It seems that it is not deleting everything from the first run through. Any thoughts?

 

[Claus Busch]

Hi,

Am Wed, 30 Oct 2013 16:04:52 -0700 (PDT) schrieb excelnoob:

I was able to get the formatting to work appropriately. Thank you! However, I noticed when it populates the Job tabs from the JobTemp tab, it seems that the JobTemp tab is not clearing out all the way. The first sheet looks good, however when it gets to the second sheet, it still has some information from the previous run through. It seems that it is not deleting everything from the first run through. Any thoughts?

I did not know that you want to clear JobTemp because any data will be
overwritten.
Try:

Sub Test()
Dim LRow As Long
Dim LrowT As Long
Dim rngC As Range
Dim varFilter() As Variant
Dim i As Integer

Application.ScreenUpdating = False
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Temp"
With Sheets("RollUp")
LRow = .Cells(Rows.Count, "E").End(xlUp).Row
.Range("E18:E" & LRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True

LrowT = Sheets("Temp").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LrowT
ReDim Preserve varFilter(LrowT - 2)
varFilter(i - 2) = Sheets("Temp").Cells(i, 1)
Next

With .Range("A18:Z" & LRow)
For i = LBound(varFilter) To UBound(varFilter)
.AutoFilter field:=5, Criteria1:=varFilter(i)
Sheets("RollUp").Range("A19:Z" & LRow).Copy
Sheets("JobTemp").Activate
Range("A19").Select
ActiveSheet.Paste link:=True
ActiveSheet.Range("A19").PasteSpecial xlPasteFormats
Sheets("JobTemp").Copy After:=Sheets(Sheets.Count)
Sheets("JobTemp (2)").Name = "Job " & varFilter(i)
With ActiveSheet
.Range("A18:Z" & .UsedRange.Rows.Count) _
.Sort key1:=Range("A18"), Order1:=xlAscending, _
Header:=xlYes
End With
Next
End With
..AutoFilterMode = False
End With

With Sheets("JobTemp")
.Range("A19:Z" & .UsedRange.Rows.Count).ClearContents
End With

Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Regards
Claus B.

 

[excelnoob]

Hi Claus,
What is happening is that when it creates an individual tab from the JobTemp template there are other codes in it. My guess was that something with the JobTemp tab was not clearing before it went to the next Job.

I need it to create the tab with only the unique job codes, say, 55555 and only 55555. The way it is working now, if the first job is 55555, it will build the sheet with only 55555 job codes. However for the next job code, say 66666, it will populate all the 66666 but there are still some 55555 codes in it. Then next tab, say code 77777, contains the 77777 codes plus some 55555 and 66666 codes.

I need it to just find all and only the 5555 and create the tab specifically for those.

If it would help, I can send you what I have. I am also getting a few more formatting bugs have popped up again. Uhg.

Thanks you again.

 

[Claus Busch]

Hi,

Am Thu, 31 Oct 2013 08:47:45 -0700 (PDT) schrieb excelnoob:

I need it to just find all and only the 5555 and create the tab specifically for those.

and you don't need the data for printing (A1:Z17) in Job # sheet?
Then try:

Sub Test()
Dim LRow As Long
Dim LrowT As Long
Dim rngC As Range
Dim varFilter() As Variant
Dim i As Integer

Application.ScreenUpdating = False
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Temp"
With Sheets("RollUp")
LRow = .Cells(Rows.Count, "E").End(xlUp).Row
.Range("E18:E" & LRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True

LrowT = Sheets("Temp").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LrowT
ReDim Preserve varFilter(LrowT - 2)
varFilter(i - 2) = Sheets("Temp").Cells(i, 1)
Next

With .Range("A18:Z" & LRow)
For i = LBound(varFilter) To UBound(varFilter)
.AutoFilter field:=5, Criteria1:=varFilter(i)
With Sheets("JobTemp")
.Activate
.Range("A19:Z" & .UsedRange.Rows.Count).ClearContents
.Range("A19").Select
End With
Sheets("RollUp").Range("A19:Z" & LRow).Copy
ActiveSheet.Paste link:=True
ActiveSheet.Range("A19").PasteSpecial xlPasteFormats
Sheets("JobTemp").Copy After:=Sheets(Sheets.Count)
Sheets("JobTemp (2)").Name = "Job " & varFilter(i)
With ActiveSheet
.Range("A18:Z" & .UsedRange.Rows.Count) _
.Sort key1:=Range("A18"), Order1:=xlAscending, _
Header:=xlYes
.Range("A1:Z17").ClearContents
End With
Sheets("RollUp").AutoFilterMode = False
Next
End With
End With

With Sheets("JobTemp")
.Range("A19:Z" & .UsedRange.Rows.Count).ClearContents
End With

Application.DisplayAlerts = False
Sheets("Temp").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[excelnoob]

It worked in the sense of populating each sheet with only one job number. Awesome! However it cleared all the text and formulas I have in rows 1-18 onthe tabs that were copied from the JobTemp tab. The Job Temp tab still hasthe text and formulas. Any suggestions?