Parse Data Sheet (anti-consolidate)
Hi everyone. I have a data sheet that 14,000 rows long. In column A
is the customer number. Then I have a "control" sheet, where I have a list of customer numbers to pull (copy) from the data sheet (A3:A20). Can VBA scan the data sheet, create a new sheet for all entries in Control("A3:A20"), and copy in the entire row for every instance found in the data sheet for each customer identified in Control("A3:A20")? Thanks! |
Parse Data Sheet (anti-consolidate)
Hi all. I was able to find this code. This takes the data sheet, and
based on the values of column a creates a sheet for each unique instance and copies the data in. Can this be modified to incorporate the list of values to do this to? Basically, The data sheet has over 300 customers in column A. I dont want to create 300 sheets! Only about 20, that will be in the list in Control("A1:A20"). Thanks!! Sub ParseData() Application.ScreenUpdating = False With Sheets("Data") lr = .Cells(Rows.Count, "a").End(xlUp).Row .Range("A1:A" & lr).AdvancedFilter Action:=xlFilterInPlace, Unique:=True For Each c In .Range("a2:a" & lr).SpecialCells(xlVisible) On Error Resume Next If Worksheets(c.Value) Is Nothing Then Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = c End If .ShowAllData .Range("a1:a" & lr).AutoFilter field:=1, Criteria1:=c dlr = Sheets(c.Value).Cells(Rows.Count, "a").End(xlUp).Row + 1 .Range("a2:a" & lr).Copy Sheets(c.Value).Range("a" & dlr) Next c .ShowAllData .Range("a1:a" & lr).AutoFilter End With Application.ScreenUpdating = True Sheets("Data").Select End Sub On Mar 19, 2:57*pm, Steve wrote: Hi everyone. *I have a data sheet that 14,000 rows long. *In column A is the customer number. *Then I have a "control" sheet, where I have a list of customer numbers to pull (copy) from the data sheet (A3:A20). Can VBA scan the data sheet, create a new sheet for all entries in Control("A3:A20"), and copy in the entire row for every instance found in the data sheet for each customer identified in Control("A3:A20")? Thanks! |
All times are GMT +1. The time now is 10:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com