Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to consolidate 580 file's data in 1 sheet without oppening all | Excel Worksheet Functions | |||
Consolidate an XLS sheet | Excel Discussion (Misc queries) | |||
trying to consolidate info into one sheet | Excel Worksheet Functions | |||
Consolidate data to summary sheet | Excel Discussion (Misc queries) | |||
How to parse the comma seperated strings into the excel sheet | Excel Programming |