a little tweaking might work?
 

Good morning,

I tried posting this yesterday but I couldn't find it. Sorry if it's
repetitive. I have some code that is taking data from 2 different tabs in
my workbook and placing it into a Summary tab but I'm having a couple of
problems that I hope are easy to fix.

1) Is there a way to adjust my column widths in the code to avoid having to
size it each time it's viewed?
2) Is there a way to format the results. Example: 349,561 shows up as 349560.8
3) The tab is created by a macro button but doesn't get recreated when you
try it for another set of user data? Is there a way to delete the tab on
return to the original tab [Source Code]? Thank you, Thank you!!! :
Here's my code:
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("Source Code").Range("A4")
.Range("C3") = Worksheets("Source Code").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Source Code").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Source Code").Range("A12")
.Range("D5") = Worksheets("Source Code").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Source Code").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("Source Code").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")

a little tweaking might work?
 

I found the answer to my column width question, but not the other two ??

"Heather" wrote:

Good morning,

I tried posting this yesterday but I couldn't find it. Sorry if it's
repetitive. I have some code that is taking data from 2 different tabs in
my workbook and placing it into a Summary tab but I'm having a couple of
problems that I hope are easy to fix.

1) Is there a way to adjust my column widths in the code to avoid having to
size it each time it's viewed?
2) Is there a way to format the results. Example: 349,561 shows up as 349560.8
3) The tab is created by a macro button but doesn't get recreated when you
try it for another set of user data? Is there a way to delete the tab on
return to the original tab [Source Code]? Thank you, Thank you!!! :
Here's my code:
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("Source Code").Range("A4")
.Range("C3") = Worksheets("Source Code").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Source Code").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Source Code").Range("A12")
.Range("D5") = Worksheets("Source Code").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Source Code").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("Source Code").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")

a little tweaking might work?
 

Query 2; you can set the format as below

Range("A1").NumberFormat = "#,##0"

Query 3 : Delete after 'End With

Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True

If this post helps click Yes
---------------
Jacob Skaria


"Heather" wrote:

I found the answer to my column width question, but not the other two ??

"Heather" wrote:

Good morning,

I tried posting this yesterday but I couldn't find it. Sorry if it's
repetitive. I have some code that is taking data from 2 different tabs in
my workbook and placing it into a Summary tab but I'm having a couple of
problems that I hope are easy to fix.

1) Is there a way to adjust my column widths in the code to avoid having to
size it each time it's viewed?
2) Is there a way to format the results. Example: 349,561 shows up as 349560.8
3) The tab is created by a macro button but doesn't get recreated when you
try it for another set of user data? Is there a way to delete the tab on
return to the original tab [Source Code]? Thank you, Thank you!!! :
Here's my code:
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("Source Code").Range("A4")
.Range("C3") = Worksheets("Source Code").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Source Code").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Source Code").Range("A12")
.Range("D5") = Worksheets("Source Code").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Source Code").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("Source Code").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")

a little tweaking might work?
 

Thank you Jacob :) got the formatting to work but I'm still trying to figure
out the Delete part. Do I create a new macro for this or add this to the end
of the other code? Sorry I'm new to all of this.

"Jacob Skaria" wrote:

Query 2; you can set the format as below

Range("A1").NumberFormat = "#,##0"

Query 3 : Delete after 'End With

Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True

If this post helps click Yes
---------------
Jacob Skaria


"Heather" wrote:

I found the answer to my column width question, but not the other two ??

"Heather" wrote:

Good morning,

I tried posting this yesterday but I couldn't find it. Sorry if it's
repetitive. I have some code that is taking data from 2 different tabs in
my workbook and placing it into a Summary tab but I'm having a couple of
problems that I hope are easy to fix.

1) Is there a way to adjust my column widths in the code to avoid having to
size it each time it's viewed?
2) Is there a way to format the results. Example: 349,561 shows up as 349560.8
3) The tab is created by a macro button but doesn't get recreated when you
try it for another set of user data? Is there a way to delete the tab on
return to the original tab [Source Code]? Thank you, Thank you!!! :
Here's my code:
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("Source Code").Range("A4")
.Range("C3") = Worksheets("Source Code").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Source Code").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Source Code").Range("A12")
.Range("D5") = Worksheets("Source Code").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Source Code").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("Source Code").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")

a little tweaking might work?
 

If you mean to delete ws = gettab("TermSummary") then insert a line between
the below two lines...


End With
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Set rPart = Worksheets("Source Code").Range("B17")

If this post helps click Yes
---------------
Jacob Skaria


"Heather" wrote:

Thank you Jacob :) got the formatting to work but I'm still trying to figure
out the Delete part. Do I create a new macro for this or add this to the end
of the other code? Sorry I'm new to all of this.

"Jacob Skaria" wrote:

Query 2; you can set the format as below

Range("A1").NumberFormat = "#,##0"

Query 3 : Delete after 'End With

Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True

If this post helps click Yes
---------------
Jacob Skaria


"Heather" wrote:

I found the answer to my column width question, but not the other two ??

"Heather" wrote:

Good morning,

I tried posting this yesterday but I couldn't find it. Sorry if it's
repetitive. I have some code that is taking data from 2 different tabs in
my workbook and placing it into a Summary tab but I'm having a couple of
problems that I hope are easy to fix.

1) Is there a way to adjust my column widths in the code to avoid having to
size it each time it's viewed?
2) Is there a way to format the results. Example: 349,561 shows up as 349560.8
3) The tab is created by a macro button but doesn't get recreated when you
try it for another set of user data? Is there a way to delete the tab on
return to the original tab [Source Code]? Thank you, Thank you!!! :
Here's my code:
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("Source Code").Range("A4")
.Range("C3") = Worksheets("Source Code").Range("B4")
' Enters the # of Users
.Range("B4") = "Users"
.Range("C4") = Worksheets("Source Code").Range("B5")
' Enters the Platform Type
.Range("B5") = "Platform/Edition"
.Range("C5") = Worksheets("Source Code").Range("A12")
.Range("D5") = Worksheets("Source Code").Range("B12")
' Enters the Addtl part numbers
Set Target = .Range("B6")
End With
Set rPart = Worksheets("Source Code").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("Source Code").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")