LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 113
Default For Each advance conflict

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Name conflict smaruzzi Excel Discussion (Misc queries) 3 October 4th 08 01:44 PM
Name Conflict Roger[_4_] Excel Discussion (Misc queries) 2 March 15th 08 01:13 AM
Name Conflict Roger[_4_] Excel Discussion (Misc queries) 5 March 14th 08 02:14 PM
Conflict Don Lloyd Excel Programming 2 October 11th 03 11:44 PM
Name conflict John Turton Excel Programming 2 August 28th 03 04:47 PM


All times are GMT +1. The time now is 01:25 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright 2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"