a little tweaking might work?

H

Heather

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")
 
J

Jacob Skaria

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 said:
I found the answer to my column width question, but not the other two >??

Heather said:
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")
Click to expand...
Click to expand...
 
H

Heather

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 said:
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 said:
I found the answer to my column width question, but not the other two >??

Heather said:
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")
Click to expand...
Click to expand...
Click to expand...
 
J

Jacob Skaria

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

Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
If this post helps click Yes
---------------
Jacob Skaria


Heather said:
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 said:
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 said:
I found the answer to my column width question, but not the other two >??

:

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")
Click to expand...
Click to expand...
Click to expand...
 
H

Heather

Thank you! :>
One more question, it does delete but then when I select the Click here to
View Summary option it's no longer available?

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

Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
If this post helps click Yes
---------------
Jacob Skaria


Heather said:
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 said:
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


:

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

:

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")
Click to expand...
Click to expand...
Click to expand...
 
J

Jacob Skaria

<<when I select the Click here to View Summary option it's no longer available?

It is a permanent delete from your workbook.

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


Heather said:
Thank you! :>
One more question, it does delete but then when I select the Click here to
View Summary option it's no longer available?

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

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

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


Heather said:
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.

:

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


:

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

:

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")
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with my code 4
Macro VBA Help please :) 7
How to create a shared procedure 3
Clear a "moving" range offset from Target 31
BeforeDoubleClick Event not working 2
Why are the two codes below not getting the right formula for myworksheet event change formula? 2
Conditional Formatting with reference to another worksheet VBA 4
Multiple entries from a drop down list 1

Top