Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
paste into new sheets
This contains ho error handling. So, if there is a sheet already
existing with the same name as one of the unique records in your filter column, you will receive an error. A simple check could be added to se if a sheet already exists. To filter on a different column, simply change the FilterColumnLetter variable to the letter of the column you want to filter on. I know it looks like a lot of code, but the vast majority of it is declaring and setting variables. Sub ReportSplit() Dim shSource As Worksheet, shTarget As Worksheet Dim rgSource As Range, rgUniques As Range Dim cl As Range Dim BotRow As Long Dim FilterColumnLetter As String Dim Uniques As Collection Dim Unique As Variant Set shSource = ActiveWorkbook.ActiveSheet Set Uniques = New Collection FilterColumnLetter = "B" BotRow = Cells(65536, _ FilterColumnLetter).End(xlUp).Row With shSource Set rgSource = .UsedRange Set rgUniques = .Range(FilterColumnLetter & "2:" _ & FilterColumnLetter & BotRow) End With On Error Resume Next For Each cl In rgUniques Uniques.Add cl.Value, CStr(cl.Value) Next cl On Error GoTo 0 Application.ScreenUpdating = False For Each Unique In Uniques Worksheets.Add after:=ActiveSheet Set shTarget = ActiveSheet shTarget.Name = Unique With rgSource .Columns(FilterColumnLetter).AutoFilter 1, Unique .Copy shTarget.Range("A1") End With shSource.AutoFilterMode = False Next Unique shSource.Activate Application.ScreenUpdating = True Set Uniques = Nothing Set shSource = Nothing Set rgSource = Nothing Set rgUniques = Nothing Set shTarget = Nothing End Sub geebee noSPAMs wrote: hi, lets say i have a sheet with like 40 rows in it. i want to group the rows by account number. so i am curious if there is any code i could write to group the account numbers, and then to see if there is code i can write to put each different group into a new sheet. thanks in advance, geebee |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste into grouped sheets using VB | Excel Worksheet Functions | |||
Copy&paste of several sheets | Excel Programming | |||
Copy&paste of several sheets | Excel Discussion (Misc queries) | |||
Paste Value Multiple Sheets | Excel Programming | |||
Copy and paste between sheets | Excel Programming |