Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How does this work?
Can anyone interpret this code for me to explain the logic in how it works.
I've run it and can see the results but I still don't understand the logic used that makes this small amount of code do so much! Sub x() Dim c As Long With Sheet1 .AutoFilterMode = False For c = 3 To .Cells(1, 1).End(xlToRight).Column .Range("A1").AutoFilter Field:=c, Criteria1:="X" Sheets.Add(after:=Sheets(Sheets.Count)).Name = Left(.Cells(1, c), 31) .AutoFilter.Range.Resize(, 2).Copy ActiveSheet.Range("A1") .ShowAllData Next c .AutoFilterMode = False End With End Sub Thanks, Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How does this work?
See comments. the code is filtering each column in sheet 1 starting in
column 3 ("C") looking for the X in each column. Then copying columns A:B to a new worksheet. The new worksheet is named the column Header in row 1. The code is naming the new worksheet the 1st 31 letters of the Header in the column. Sub x() Dim c As Long With Sheet1 'turn oiff all autofilter on worksheet .AutoFilterMode = False 'look at all columns where there data in row 1 'start at column 3 and move to last column with data 'C is the column Number starting at 3 For c = 3 To .Cells(1, 1).End(xlToRight).Column 'auto filter column C - the filed is the column .Range("A1").AutoFilter Field:=c, Criteria1:="X" 'add new workshee to workbook 'the sheet name is the 1st 31 chaaracter in row 1, column C Sheets.Add(after:=Sheets(Sheets.Count)).Name = Left(.Cells(1, c), 31) 'copy the first two columns of the autofiler rows 'to the new worksheet .AutoFilter.Range.Resize(, 2).Copy ActiveSheet.Range("A1") 'remove the autofilter .ShowAllData Next c .AutoFilterMode = False End With End Sub "leimst" wrote: Can anyone interpret this code for me to explain the logic in how it works. I've run it and can see the results but I still don't understand the logic used that makes this small amount of code do so much! Sub x() Dim c As Long With Sheet1 .AutoFilterMode = False For c = 3 To .Cells(1, 1).End(xlToRight).Column .Range("A1").AutoFilter Field:=c, Criteria1:="X" Sheets.Add(after:=Sheets(Sheets.Count)).Name = Left(.Cells(1, c), 31) .AutoFilter.Range.Resize(, 2).Copy ActiveSheet.Range("A1") .ShowAllData Next c .AutoFilterMode = False End With End Sub Thanks, Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
HOW TO MAKE A LIST OF WORK SHEET IN WORK BOOK IN EXCEL 2007 | Excel Programming | |||
If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel |