Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with tweaking formula | Excel Worksheet Functions | |||
Tweaking formula? | Excel Discussion (Misc queries) | |||
Tweaking Code | Excel Programming | |||
Tweaking Code | Excel Programming | |||
Formula needs tweaking - HELP!! | Excel Programming |