help with macro - creating new columns/copying/creating new worksheets from default

[vsantoro]

have this macro, but the column names have changed in the excel, this macro scrolls by number of colums, how do i change to use column header names instead of number of columns ? also each time its run have to change date in macro, any way to make this selectable in excel before run macro ?


Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=12
Selection.AutoFilter Field:=15, Criteria1:="=GE*", Operator:=xlAnd, _
Criteria2:="<>GE Energy - Hydro"
ActiveWindow.ScrollColumn = 1
Cells.Select
Selection.Copy
Sheets.Add.Name = "Default Data"
ActiveSheet.Paste
Columns("A:AZ").Select
Application.CutCopyMode = False
Selection.ColumnWidth = 20
Rows("1:1").Select
Selection.AutoFilter
Worksheets("Default").Delete

ActiveWindow.SmallScroll ToRight:=1
Selection.AutoFilter Field:=6, Criteria1:=">07/12/2010", Operator:=xlAnd, _
Criteria2:="<07/19/2010"
ActiveWindow.SmallScroll Down:=159
ActiveWindow.ScrollRow = 1
Selection.AutoFilter Field:=6, Criteria1:=">07/12/2010 0:00", Operator:= _
xlAnd, Criteria2:="<07/19/2010 0:00"
Cells.Select
Range("B1").Activate
Selection.Copy
Sheets.Add.Name = "Percent OT"
Range("A1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=39
Columns("A:AZ").Select
Range("AZ1").Activate
Selection.ColumnWidth = 20
ActiveWindow.ScrollColumn = 1
Range("D2").Select
ActiveWindow.SmallScroll Down:=-6
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=14
Selection.AutoFilter Field:=18, Criteria1:="<=0", Operator:=xlAnd
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-6
Sheets("Default Data").Select
Rows("1:1").Select
Range("B1").Activate
Selection.AutoFilter
Rows("1:1").Select
Range("B1").Activate
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=2
Selection.AutoFilter Field:=5, Criteria1:="<>c", Operator:=xlAnd
Selection.AutoFilter Field:=6, Criteria1:="<07/19/2010", Operator:=xlAnd
Cells.Select
Range("C1").Activate
Selection.Copy
Sheets.Add.Name = "Overdue"
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=45
Columns("A:AZ").Select
Range("AZ1").Activate
Selection.ColumnWidth = 20
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-3
ActiveWindow.SmallScroll ToRight:=6
Rows("1:1").Select
Range("G1").Activate
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWindow.SmallScroll ToRight:=14
Range("R2").Select
Selection.Sort Key1:=Range("R2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-6
Range("B2").Select
Sheets("Default Data").Select
Rows("1:1").Select
Range("C1").Activate
Selection.AutoFilter
Selection.AutoFilter
Columns("D:D").Select
ActiveWindow.SmallScroll ToRight:=13
Selection.AutoFilter Field:=17, Criteria1:=">07/12/2010", Operator:=xlAnd _
, Criteria2:="<07/19/2010"
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=174
ActiveWindow.ScrollRow = 1
Cells.Select
Selection.Copy
Sheets.Add.Name = "Span"
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 24
ActiveWindow.SmallScroll ToRight:=17
Columns("A:AZ").Select
Range("AZ1").Activate
Selection.ColumnWidth = 20
ActiveWindow.ScrollColumn = 1
Sheets("Default Data").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="<>c", Operator:=xlAnd
Selection.AutoFilter Field:=6, Criteria1:=">07/19/2010", Operator:=xlAnd, _
Criteria2:="<07/26/2010"
Cells.Select
Selection.Copy
Sheets.Add.Name = "Lookahead"
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 24
ActiveWindow.SmallScroll ToRight:=16
Columns("A:AZ").Select
Range("AZ1").Activate
Selection.ColumnWidth = 20
ActiveWindow.ScrollColumn = 1
Range("A1").Select

Sheets("Default Data").Select
Selection.AutoFilter
Range("A1").Select
Sheets("Span").Select
Range("A1").Select
Sheets("Overdue").Select
Range("A1").Select
Sheets("Percent OT").Select
Range("A1").Select
End Sub

[TMS]

it would help if you uploaded the workbook, or a representative sample, together with details about what columns have changed and how ... have you inserted columns, deleted columns, changed the headings, etc? Ideally, a before and after (current) copy.

It's difficult to check the working without the structure and some typical data, and it's even harder to make that up.

I'm guessing that you recorded this macro as you undertook whatever the task was and you've just repeated it each time since. Yes?

Regards

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[vsantoro]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

sorry about that , will remember that next post. I have edited title so it is more specific.

[vsantoro]

it would help if you uploaded the workbook, or a representative sample, together with details about what columns have changed and how ... have you inserted columns, deleted columns, changed the headings, etc? Ideally, a before and after (current) copy.

It's difficult to check the working without the structure and some typical data, and it's even harder to make that up.

I'm guessing that you recorded this macro as you undertook whatever the task was and you've just repeated it each time since. Yes?

Regards

Hi, this macro just gets repeated , when the xls is generated the data is different and macro is run against it. i uploaded the xls that is generated and the macro should be run against.

[TMS]

Sorry it's taken a while to reply; I've spent some time working on your current code.

With regard to filtering based on the column headings, one approach is the following function which will accept a column header and a range and return a column number using the MATCH function.

Please Login or Register  to view this content.

The subroutine following is a simple test of the function:

Please Login or Register  to view this content.

An example of how this is used:

Please Login or Register  to view this content.

As I said, I've amended the code; see below. I have removed all the scrolling and most of the selecting and generally tidied it up. I have adjusted the filter columns where it was, or seemed, obvious but I couldn't guess at some of them.

Finally, rather than hard coding dates in the body of the code, I would put them on a "control sheet" and initialise variables as the first stage of the macro execution. Alternatively, you could define some variables at the beginning (top) of your code and change the values there once. That would save ploughing through all the code ... too easy to miss something.

The comments serve to "self document" the code

Hope this helps.

Regards

Please Login or Register  to view this content.

Enjoy!

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[TMS]

@RoyUK Sorry, which bit of code wasn't tagged ?