help with my code
 

Hi, I am wondering if someone might know of a way to tweak this a little. You
guys have been so helpful :)

1) I am having trouble with the column widths, each time it's run it removes
the width, is there a way to set this in the code?
2) Is there a way to have the currency in Column D show up looking like it
does in the other tab (example 14,995) rather than 14995.1
3) When they hit the macro button and it creates this information it needs
to do 2 things 1) dump into Word and 2) clear so it can create the tab again
with new data?

Any help is always appreciated! -- thank you!

Sub CreateTerm()
Dim ws As Worksheet
Set ws = gettab("TermSummary")
Dim rPart As Range
Dim Target As Range
With ws

' Enters the Currency
.Range("B3") = Worksheets("Planner").Range("A4")
.Range("C3") = Worksheets("Planner").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Planner").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Planner").Range("A12")
.Range("D5") = Worksheets("Planner").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Planner").Range("B17")
Do Until rPart = ""
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop

' Enters the Grand Total Price
Target.Offset(, 0) = "Term Model Total Price"
Target.Offset(, 2) = Worksheets("Planner").Range("TermModel.Price")
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)

' Enters the SW tools
Target.Offset(, 0) = "SW Tools"
Target.Offset(, 2) = Worksheets("SW Tools").Range("B13")

help with my code
 

Heather, I would be more inclined to help you except that you didn't even
bother to thank me for the help already provided for the index formula in
your "if and lookup" question. The answer to this is easy.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Heather" wrote in message
...
Hi, I am wondering if someone might know of a way to tweak this a little.
You
guys have been so helpful :)

1) I am having trouble with the column widths, each time it's run it
removes
the width, is there a way to set this in the code?
2) Is there a way to have the currency in Column D show up looking like it
does in the other tab (example 14,995) rather than 14995.1
3) When they hit the macro button and it creates this information it needs
to do 2 things 1) dump into Word and 2) clear so it can create the tab
again
with new data?

Any help is always appreciated! -- thank you!

Sub CreateTerm()
Dim ws As Worksheet
Set ws = gettab("TermSummary")
Dim rPart As Range
Dim Target As Range
With ws

' Enters the Currency
.Range("B3") = Worksheets("Planner").Range("A4")
.Range("C3") = Worksheets("Planner").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Planner").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Planner").Range("A12")
.Range("D5") = Worksheets("Planner").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Planner").Range("B17")
Do Until rPart = ""
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop

' Enters the Grand Total Price
Target.Offset(, 0) = "Term Model Total Price"
Target.Offset(, 2) = Worksheets("Planner").Range("TermModel.Price")
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)

' Enters the SW tools
Target.Offset(, 0) = "SW Tools"
Target.Offset(, 2) = Worksheets("SW Tools").Range("B13")

help with my code
 

I'm so sorry about that.. I've been going in so many directions today that I
haven't had a chance to read your answers .. I'll go look now :)

"Don Guillett" wrote:

Heather, I would be more inclined to help you except that you didn't even
bother to thank me for the help already provided for the index formula in
your "if and lookup" question. The answer to this is easy.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Heather" wrote in message
...
Hi, I am wondering if someone might know of a way to tweak this a little.
You
guys have been so helpful :)

1) I am having trouble with the column widths, each time it's run it
removes
the width, is there a way to set this in the code?
2) Is there a way to have the currency in Column D show up looking like it
does in the other tab (example 14,995) rather than 14995.1
3) When they hit the macro button and it creates this information it needs
to do 2 things 1) dump into Word and 2) clear so it can create the tab
again
with new data?

Any help is always appreciated! -- thank you!

Sub CreateTerm()
Dim ws As Worksheet
Set ws = gettab("TermSummary")
Dim rPart As Range
Dim Target As Range
With ws

' Enters the Currency
.Range("B3") = Worksheets("Planner").Range("A4")
.Range("C3") = Worksheets("Planner").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Planner").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Planner").Range("A12")
.Range("D5") = Worksheets("Planner").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Planner").Range("B17")
Do Until rPart = ""
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop

' Enters the Grand Total Price
Target.Offset(, 0) = "Term Model Total Price"
Target.Offset(, 2) = Worksheets("Planner").Range("TermModel.Price")
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)

' Enters the SW tools
Target.Offset(, 0) = "SW Tools"
Target.Offset(, 2) = Worksheets("SW Tools").Range("B13")

help with my code
 

I sent you a file YESTERDAY .

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Heather" wrote in message
...
I'm so sorry about that.. I've been going in so many directions today that
I
haven't had a chance to read your answers .. I'll go look now :)

"Don Guillett" wrote:

Heather, I would be more inclined to help you except that you didn't even
bother to thank me for the help already provided for the index formula in
your "if and lookup" question. The answer to this is easy.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Heather" wrote in message
...
Hi, I am wondering if someone might know of a way to tweak this a
little.
You
guys have been so helpful :)

1) I am having trouble with the column widths, each time it's run it
removes
the width, is there a way to set this in the code?
2) Is there a way to have the currency in Column D show up looking like
it
does in the other tab (example 14,995) rather than 14995.1
3) When they hit the macro button and it creates this information it
needs
to do 2 things 1) dump into Word and 2) clear so it can create the tab
again
with new data?

Any help is always appreciated! -- thank you!

Sub CreateTerm()
Dim ws As Worksheet
Set ws = gettab("TermSummary")
Dim rPart As Range
Dim Target As Range
With ws

' Enters the Currency
.Range("B3") = Worksheets("Planner").Range("A4")
.Range("C3") = Worksheets("Planner").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Planner").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Planner").Range("A12")
.Range("D5") = Worksheets("Planner").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Planner").Range("B17")
Do Until rPart = ""
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop

' Enters the Grand Total Price
Target.Offset(, 0) = "Term Model Total Price"
Target.Offset(, 2) = Worksheets("Planner").Range("TermModel.Price")
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)

' Enters the SW tools
Target.Offset(, 0) = "SW Tools"
Target.Offset(, 2) = Worksheets("SW Tools").Range("B13")

help with my code
 

Have a look in the vba help index for AUTOFIT ie:
columns(2).autofit


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Heather" wrote in message
...
Hi, I am wondering if someone might know of a way to tweak this a little.
You
guys have been so helpful :)

1) I am having trouble with the column widths, each time it's run it
removes
the width, is there a way to set this in the code?
2) Is there a way to have the currency in Column D show up looking like it
does in the other tab (example 14,995) rather than 14995.1
3) When they hit the macro button and it creates this information it needs
to do 2 things 1) dump into Word and 2) clear so it can create the tab
again
with new data?

Any help is always appreciated! -- thank you!

Sub CreateTerm()
Dim ws As Worksheet
Set ws = gettab("TermSummary")
Dim rPart As Range
Dim Target As Range
With ws

' Enters the Currency
.Range("B3") = Worksheets("Planner").Range("A4")
.Range("C3") = Worksheets("Planner").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Planner").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Planner").Range("A12")
.Range("D5") = Worksheets("Planner").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Planner").Range("B17")
Do Until rPart = ""
Target.Offset(, 1) = rPart.Offset(, -1).Value
Target.Offset(, 2) = rPart.Offset(, 1)
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)
Loop

' Enters the Grand Total Price
Target.Offset(, 0) = "Term Model Total Price"
Target.Offset(, 2) = Worksheets("Planner").Range("TermModel.Price")
Set Target = Target.Offset(1)
Set rPart = rPart.Offset(1)

' Enters the SW tools
Target.Offset(, 0) = "SW Tools"
Target.Offset(, 2) = Worksheets("SW Tools").Range("B13")