Home |
Search |
Today's Posts |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
It works now, Im not realy sure what was wrong but I know that
improper declarations and sequencing had something to do with it. Here is the final Code if your feeling brave :) Sub BreakoutSheets() Dim colHeadCol As Variant Dim colHead As Variant Dim srchCol As Variant Dim strRngOld As Range Dim strRng As Range Dim x As Integer Dim y As Integer Dim rng As Range Dim firstaddress As String Dim rng2 As Range 'colHeadCol = Array("code") 'test set colHeadCol = Array("CONFIG", "TYPE", "MO_Number", "CODE") 'complete set For Each colHead In colHeadCol 'select prepared worksheet Worksheets("Prepared").Activate 'Search the header row for the column heading Rows(1).Select 'assign variable to column heading srchCol = Selection.find(What:=colHead, After:=ActiveCell, LookIn:=xlValues, Lookat:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Offset(1, 0).Address Range(srchCol).Select 'include used Column cells address in variable Set srchCol = Range(srchCol, Selection.End(xlDown)) '.Address 'set strRngOld to something for the following test Set strRngOld = Range("a1") For Each strRng In srchCol 'Sets strRng to the the previous strRng If Not strRngOld = "CODE" Then Set strRng = strRngOld End If If strg = strRng Then Do Until strg < strRng strRng.Activate ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Set strRng = ActiveCell x = 0 y = 0 strRng.Offset(rowOffset:=y, columnOffset:=x).Activate y = y + 1 If strRng.Value = Empty Then Exit Sub End If strRng.Select Loop End If 'Note location of current strRng Set strRngOld = strRng With srchCol strg = strRng If WorksheetExists(strg, ActiveWorkbook) Then GoTo SkipSheet Else Set rng = .find(What:=strg, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ Lookat:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then firstaddress = rng.Address Set rng2 = rng Do If rng2 Is Nothing Then Set rng2 = rng Else Set rng2 = Application.Union(rng2, rng) End If Set rng = .FindNext(rng) Loop While Not rng Is Nothing And rng.Address < firstaddress End If End If End With 'Select all cells If Not rng2 Is Nothing Then rng2.Select Selection.EntireRow.Copy PasteNewSheet 'Pastes new sheet and calls other setup procedures Next SkipSheet: Next End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Name conflict | Excel Discussion (Misc queries) | |||
Name Conflict | Excel Discussion (Misc queries) | |||
Name Conflict | Excel Discussion (Misc queries) | |||
Conflict | Excel Programming | |||
Name conflict | Excel Programming |