X+ setup sheet question

[neurosis]

I have a quick question and hopefully an easy one. I dont know anything about excel except for how to change values in cells so basically nothing. What I am trying to do is have it export a tool list that doesnt have every single operation showing. I would just like a setup sheet (the basic output from X+ works fine for now) with a tool list at the bottom of the page that only has the tools listed once. Not every operation that the tool does. Is this an easy fix?

[Midwest]

X+ has a create tool list........will this work for ya????

[Zoober]

Which template (.xss/.xlt) are you using?

I have a macro that deletes duplicate tools and reorders them, but based on the template you are using, you may need to tweak.

[PeterM]

I know that it's a small pain, but x+ lists the tools that are Selected, also, if you ghost out a toolpath x+ won't list it. I find it easy enough to use those two actions to make setup sheets of moderatly simple programs I don't like the tool list feature because it will not print the tools in order.

[neurosis]

I am apparently using the .xss I dont have a preference as of yet. Is one better than the other? easier to modify if needed? I need to learn how to just do this myself??

 

I'd like to learn how to do this but have never really had a need to worry about it. Are there any good resources to help a newbie out?

[neurosis]

quote:

---

I know that it's a small pain, but x+ lists the tools that are Selected, also, if you ghost out a toolpath x+ won't list it. I find it easy enough to use those two actions to make setup sheets of moderatly simple programs I don't like the tool list feature because it will not print the tools in order.

---

I think that you can resort the tool list before printing. I think that is just as simple as clicking on the tool # column above the list.

 

I am assuming that you are talking about listing the tools in order "1-10" not in the order used.

[Zoober]

The .xss calls a .xlt file of the same name.

ie: millsetupsheet.xss calls millsetupsheet.xlt

The .xlt is the "real" template, and where the formatting macros are located.

[PeterM]

"I think that you can resort the tool list before printing. I think that is just as simple as clicking on the tool # column above the list."

 

Thanks, that never occurred to me

[neurosis]

quote:

---

The .xss calls a .xlt file of the same name.

ie: millsetupsheet.xss calls millsetupsheet.xlt

The .xlt is the "real" template, and where the formatting macros are located.

---

Is it easy to write a macro to delete duplicate tools?

[Zoober]

This is what mine looks like.

If it intimidates you, I'd say no, it's not easy.

If you understand the code below, I'd say yea, it's easy.

After it's created, you need to call from other subs that you have selected correct cells from.

Prolly not for the VB sqeamish.

 

code:

---

 Public Sub DeleteDuplicateRows()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' DeleteDuplicateRows

' This will delete duplicate records, based on the Active Column. That is,

' if the same value is found more than once in the Active Column, all but

' the first (lowest row number) will be deleted.

'

' To run the macro, select the entire column you wish to scan for

' duplicates, and run this procedure.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

Dim R As Long

Dim N As Long

Dim V As Variant

Dim Rng As Range

 

On Error GoTo EndMacro

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

 

 

Set Rng = Application.Intersect(ActiveSheet.UsedRange, _

ActiveSheet.Columns(ActiveCell.Column))

 

Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")

 

N = 0

For R = Rng.Rows.Count To 2 Step -1

If R Mod 500 = 0 Then

Application.StatusBar = "Processing Row: " & Format(R, "#,##0")

End If

 

V = Rng.Cells(R, 1).Value

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.

' Rather than pass in the variant, you need to pass in vbNullString explicitly.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If V = vbNullString Then

If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then

Rng.Rows®.EntireRow.Delete

N = N + 1

End If

Else

If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then

Rng.Rows®.EntireRow.Delete

N = N + 1

End If

End If

Next R

 

EndMacro:

 

Application.StatusBar = False

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

'MsgBox "Duplicate Rows Deleted: " & CStr(N)

 

End Sub

---

[crazy^millman]

Robert if you are using open rows in your set-up sheet your method work great. I however am using closed rows from a standard set-up sheet we use. I need to do a lot more formatting to the example you sent me, but after I looked at it off and on for a couple days it started to make sense. When I get it all done I will send you my finally results.

[Zoober]

Ron, I'm not quite sure what you mean by "open rows" and "closed rows", but there are range methods to select ranges of rows depending on weather or not there are gaps (no information in a row) in the range of a column.

Is that what you mean. Sorry, its getting late and I got 11 hrs of 5ax in meh head.

[neurosis]

Yeap, that looks too complicated for me. Did that start as a recorded macro or was it all hand written? There has to be a simple way to do this isnt there?

[Zoober]

I actually pirated that from one of my other spreadsheets that I delete dupes from. Had to massage it to fit this need. Hand written tho. Pretty difficult to record range selections.

[crazy^millman]

What I mean is the set-up sheet output may have 45 rows. In my case that would be 3 sheets not one so it then needs to filter the tools not only by number, but also by amount per my set-up page template that I use. So what I mean by open is that in your set-up if you get 45 that macro places then all on the one page. I need to make the macro cut the 1st 20 to one sheet then take the next 20 to a sheet and then the last 5 to a sheet if I get 45 tools as an output. You will get all 45 on one sheet and if I am going to automate it I want it to do 20 sheets if I have 400 tools programmed. I hope that makes sense.

 

What you sent me was a big help and was like a light bulb going off after all these years of messing with Macros for Excel and not making much sense this did.

 

[ 02-14-2008, 10:39 AM: Message edited by: Crazy^Millman ]

[Zoober]

Ah, I get it.

I just allow it to run into multiple pages per sheet. If I have more tools than one page allows, it just flows into the next page of that sheet. Same with my seq sheet. You can see this by hitting print preview. I regularly run >50 tools, and that works pretty good for me, and I get the same header on each page.

But I realize that my way is not the best way (or even close) to doing it.

Gunther has provided a great tool for all to work with! He gets all the props.

I am glad to be able to help....