For Each c In Range... is slow
 

This works but is a bit clunky and a little slow.

With sheet Reg:
Columns A, B, C is Name, Info1, Info2 (a person & two info bits per row)
Columns D, E, F, G, H, I are Sheet1, Sheet2, Sheet3 etc. actual sheet names in the workbook, just using six here, could be ~15.

If any of the cells in the Sheet Name Header columns have an "X" then the column A, B, C data on that row is copied to the sheet named in the Header of that column.

I have set OneRng to include D2 to I- as far down as column A info goes.
Then a For Each / Next offsetting from each "X" to copy the A B C info of that row and paste in the sheet name of that column.

I'm guessing the search method for looking across the OneRng is not very efficient using this approach.

Got a smoother idea, please?

Howard


Sub Header_Sheet()
Dim OneRng As Range
Dim sheetTo As String
Dim c As Range
Dim aRow As Long, aCol As Long


Set OneRng = Sheets("Reg").Range("D2:I" & Cells(Rows.Count, "A").End(xlUp).Row)
Application.ScreenUpdating = False

For Each c In OneRng
aRow = c.Row - 1
aCol = c.Column - 1

sheetTo = c.Offset(-aRow, 0).Value

If c = "X" Then
c.Offset(, -aCol).Resize(1, 3).Copy Sheets(sheetTo).Range("A" & Rows.Count).End(xlUp)(2)
End If

Next 'c
Application.ScreenUpdating = True
End Sub

For Each c In Range... is slow
 

Hi Howard,

Am Sat, 26 Sep 2015 03:15:09 -0700 (PDT) schrieb L. Howard:

I'm guessing the search method for looking across the OneRng is not very efficient using this approach.

try again autofilter:

Sub Test()
Dim LCol As Long, LRow As Long, i As Long
Dim myRng As Range

Application.ScreenUpdating = False

With ActiveSheet
LCol = .Cells(1, Columns.Count).End(xlToLeft).Column
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set myRng = .Range(.Cells(1, 1), .Cells(LRow, LCol))

For i = 4 To LCol
myRng.AutoFilter Field:=i, Criteria1:="<"
.Range(.Cells(2, 1), .Cells(LRow, 3)).SpecialCells(xlCellTypeVisible).Copy _
Sheets(.Cells(1, i).Value).Cells(Rows.Count, 1).End(xlUp)(2)
.ShowAllData
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

For Each c In Range... is slow
 

try again autofilter:

Sub Test()
Dim LCol As Long, LRow As Long, i As Long
Dim myRng As Range

Application.ScreenUpdating = False

With ActiveSheet
LCol = .Cells(1, Columns.Count).End(xlToLeft).Column
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set myRng = .Range(.Cells(1, 1), .Cells(LRow, LCol))

For i = 4 To LCol
myRng.AutoFilter Field:=i, Criteria1:="<"
.Range(.Cells(2, 1), .Cells(LRow, 3)).SpecialCells(xlCellTypeVisible).Copy _
Sheets(.Cells(1, i).Value).Cells(Rows.Count, 1).End(xlUp)(2)
.ShowAllData
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--

Well, that is a work of art. Autofilter to the rescue again, and of course the excellent knowledge to use it.

Thanks Claus.

Howard