Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data from one sheet to many based on column A
Hello 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")? I have some code below that looks at the data sheet, and based on the value in column A creates a sheet for each unique instance and copies the data in. Can this be modified to incorporate the list of values in the Control sheet? 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data from one sheet to many based on column A
Try this one Steve
http://www.rondebruin.nl/copy5.htm#sheet -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Steve" wrote in message ... Hello 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")? I have some code below that looks at the data sheet, and based on the value in column A creates a sheet for each unique instance and copies the data in. Can this be modified to incorporate the list of values in the Control sheet? 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data from one sheet to many based on column A
Thanks Ron! Is there a way to only create sheets and copy data for
specific values in column A? The number of unique values that I have is about 300...I dont want to create 300 sheets! I'm really only interested in copying out about 20 of the customers. I have the customer numbers listed in a sheet called "control", range a1:a20. It is also a named range called "customer". Thanks again for your help!! On Mar 21, 10:12*am, "Ron de Bruin" wrote: Try this one Stevehttp://www.rondebruin.nl/copy5.htm#sheet -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Steve" wrote in ... Hello 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")? I have some code below that looks at the data sheet, and based on the value in column A creates a sheet for each unique instance and copies the data in. *Can this be modified to incorporate the list of values in the Control sheet? *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- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data from one sheet to many based on column A
Yes, I will post a example after dinner
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Steve" wrote in message ... Thanks Ron! Is there a way to only create sheets and copy data for specific values in column A? The number of unique values that I have is about 300...I dont want to create 300 sheets! I'm really only interested in copying out about 20 of the customers. I have the customer numbers listed in a sheet called "control", range a1:a20. It is also a named range called "customer". Thanks again for your help!! On Mar 21, 10:12 am, "Ron de Bruin" wrote: Try this one Stevehttp://www.rondebruin.nl/copy5.htm#sheet -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Steve" wrote in ... Hello 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")? I have some code below that looks at the data sheet, and based on the value in column A creates a sheet for each unique instance and copies the data in. Can this be modified to incorporate the list of values in the Control sheet? 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- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data from one sheet to many based on column A
Thanks Ron!!
On Mar 21, 10:41*am, "Ron de Bruin" wrote: Yes, I will post a example after dinner -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Steve" wrote in ... Thanks Ron! *Is there a way to only create sheets and copy data for specific values in column A? *The number of unique values that I have is about 300...I dont want to create 300 sheets! *I'm really only interested in copying out about 20 of the customers. *I have the customer numbers listed in a sheet called "control", range a1:a20. *It is also a named range called "customer". *Thanks again for your help!! On Mar 21, 10:12 am, "Ron de Bruin" wrote: Try this one Stevehttp://www.rondebruin.nl/copy5.htm#sheet -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Steve" wrote in ... Hello 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")? I have some code below that looks at the data sheet, and based on the value in column A creates a sheet for each unique instance and copies the data in. Can this be modified to incorporate the list of values in the Control sheet? 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data from one sheet to many based on column A
Try this Steve
Sub Copy_To_Worksheets_Test() Dim CalcMode As Long Dim ws1 As Worksheet Dim ws2 As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim cell As Range Dim Lrow As Long Dim FieldNum As Integer 'Name of the sheet with your data Set ws1 = Sheets("Sheet1") '<<< Change 'Set filter range : A1 is the top left cell of your filter range and 'the header of the first column, D is the last column in the filter range Set rng = ws1.Range("A1:D" & Rows.Count) 'Set Field number of the filter column 'This example filters on the first field in the range(change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... FieldNum = 1 With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ' Worksheet with the list of customers numbers in column A Set ws2 = Worksheets("control") With ws2 'loop through the list in ws2 and filter/copy to a new sheet Lrow = .Cells(Rows.Count, "A").End(xlUp).Row For Each cell In .Range("A1:A" & Lrow) Set WSNew = Sheets.Add On Error Resume Next WSNew.Name = cell.Value If Err.Number 0 Then MsgBox "Change the name of : " & WSNew.Name & " manually" Err.Clear End If On Error GoTo 0 'Firstly, remove the AutoFilter ws1.AutoFilterMode = False 'Filter the range rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value 'Copy the visible data and use PasteSpecial to paste to the new worksheet ws1.AutoFilter.Range.Copy With WSNew.Range("A1") ' Paste:=8 will copy the columnwidth in Excel 2000 and higher .PasteSpecial Paste:=8 .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False .Select End With 'Close AutoFilter ws1.AutoFilterMode = False Next cell 'Delete the ws2 sheet On Error Resume Next Application.DisplayAlerts = False .Delete Application.DisplayAlerts = True On Error GoTo 0 End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Steve" wrote in message ... Thanks Ron!! On Mar 21, 10:41 am, "Ron de Bruin" wrote: Yes, I will post a example after dinner -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Steve" wrote in ... Thanks Ron! Is there a way to only create sheets and copy data for specific values in column A? The number of unique values that I have is about 300...I dont want to create 300 sheets! I'm really only interested in copying out about 20 of the customers. I have the customer numbers listed in a sheet called "control", range a1:a20. It is also a named range called "customer". Thanks again for your help!! On Mar 21, 10:12 am, "Ron de Bruin" wrote: Try this one Stevehttp://www.rondebruin.nl/copy5.htm#sheet -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Steve" wrote in ... Hello 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")? I have some code below that looks at the data sheet, and based on the value in column A creates a sheet for each unique instance and copies the data in. Can this be modified to incorporate the list of values in the Control sheet? 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Data from one sheet to many based on column A
Thanks guys!!
On Mar 21, 11:57*am, "Ron de Bruin" wrote: Try thisSteve Sub Copy_To_Worksheets_Test() * * Dim CalcMode As Long * * Dim ws1 As Worksheet * * Dim ws2 As Worksheet * * Dim WSNew As Worksheet * * Dim rng As Range * * Dim cell As Range * * Dim Lrow As Long * * Dim FieldNum As Integer * * 'Name of the sheet with your data * * Set ws1 = Sheets("Sheet1") *'<<< Change * * 'Set filter range : A1 is the top left cell of your filter range and * * 'the header of the first column, D is the last column in the filter range * * Set rng = ws1.Range("A1:D" & Rows.Count) * * 'Set Field number of the filter column * * 'This example filters on the first field in the range(change the field if needed) * * 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... * * FieldNum = 1 * * With Application * * * * CalcMode = .Calculation * * * * .Calculation = xlCalculationManual * * * * .ScreenUpdating = False * * End With * * ' Worksheet with the list of customers numbers in column A * * Set ws2 = Worksheets("control") * * With ws2 * * * * 'loop through the *list in ws2 and filter/copy to a new sheet * * * * Lrow = .Cells(Rows.Count, "A").End(xlUp).Row * * * * For Each cell In .Range("A1:A" & Lrow) * * * * * * Set WSNew = Sheets.Add * * * * * * On Error Resume Next * * * * * * WSNew.Name = cell.Value * * * * * * If Err.Number 0 Then * * * * * * * * MsgBox "Change the name of : " & WSNew.Name & " manually" * * * * * * * * Err.Clear * * * * * * End If * * * * * * On Error GoTo 0 * * * * * * 'Firstly, remove the AutoFilter * * * * * * ws1.AutoFilterMode = False * * * * * * 'Filter the range * * * * * * rng.AutoFilter Field:=FieldNum, Criteria1:="=" & cell.Value * * * * * * 'Copy the visible data and use PasteSpecial to paste to the new worksheet * * * * * * ws1.AutoFilter.Range.Copy * * * * * * With WSNew.Range("A1") * * * * * * * * ' Paste:=8 will copy the columnwidth in Excel 2000 and higher * * * * * * * * .PasteSpecial Paste:=8 * * * * * * * * .PasteSpecial xlPasteValues * * * * * * * * .PasteSpecial xlPasteFormats * * * * * * * * Application.CutCopyMode = False * * * * * * * * .Select * * * * * * End With * * * * * * 'Close AutoFilter * * * * * * ws1.AutoFilterMode = False * * * * Next cell * * * * 'Delete the ws2 sheet * * * * On Error Resume Next * * * * Application.DisplayAlerts = False * * * * .Delete * * * * Application.DisplayAlerts = True * * * * On Error GoTo 0 * * End With * * With Application * * * * .ScreenUpdating = True * * * * .Calculation = CalcMode * * End With End Sub -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Steve" wrote in ... Thanks Ron!! On Mar 21, 10:41 am, "Ron de Bruin" wrote: Yes, I will post a example after dinner -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Steve" wrote in ... Thanks Ron! Is there a way to only create sheets and copy data for specific values in column A? The number of unique values that I have is about 300...I dont want to create 300 sheets! I'm really only interested in copying out about 20 of the customers. I have the customer numbers listed in a sheet called "control", range a1:a20. It is also a named range called "customer". Thanks again for your help!! On Mar 21, 10:12 am, "Ron de Bruin" wrote: Try this oneStevehttp://www.rondebruin.nl/copy5.htm#sheet -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Steve" wrote in ... Hello 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")? I have some code below that looks at the data sheet, and based on the value in column A creates a sheet for each unique instance and copies the data in. Can this be modified to incorporate the list of values in the Control sheet? 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Copy rows of one sheet into mutiple sheets based on column value | Excel Discussion (Misc queries) | |||
MACRO - copy rows based on value in column to another sheet | Excel Discussion (Misc queries) | |||
copy to another sheet based on column value | Excel Programming | |||
MACRO - copy rows based on value in column to another sheet | Excel Programming |