98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Creating new sheet with values based on custom functions
Creating new sheet with values based on custom functions
Resolved · High Priority · Version 2007
Wed 11 Jul 2012, 09:43Delegate Steven said...
Steven has attended:
Access Intermediate course
Creating new sheet with values based on custom functions
I have a large data table that has been created using vba from a sheet provided from an external customer.
an example of part of the table looks like this -
T64_Boxed_Intake 53412 160302 155940 94368
T64_Boxed_Despatch 85182 86439 115892 92518
T71_Boxed_Intake 164445 105531 149010 98010
T71_Boxed_Despatch 90337 69966 88311 87696
The values from left to right are weekly figures.
The T"" on the left are different clothing styles.
I need to create two more sheets where if the T number is T64, T71, T74, T78, T86 or T88 i must copy the row which ends with _Despatch and do (value*.8) and on the other sheet (value*.2)
I have written custom functions called StorePick which is the 80% value and Franchise which is the 20% value.
The sheet i am taking the data from is a variable called (Export) and the sheets i would like to create will be called ExportStore(80% values) and ExportFran(20% values)
Could you please provide some sample code on how i can first check the T number and if it is one of the codes i have listed above copy the _Despatch row and apply both functions, one to each newly created sheet?
Thanks & Regards
Steve
Mon 16 Jul 2012, 10:34Trainer Anthony said...
RE: Creating new sheet with values based on custom functions
Hi Steven, thanks for your query. For us to write the code of this nature for you would take time and involve a cost, I'm afraid. You also mention Sheets and Tables so it's a little unclear whether you want to do this in Access or Excel. In Excel this would involve looping down through the range, parsing the first three characters, copying the relevant row to a new sheet and doing the maths.
In Access you'd do it a little differently with queries. I would still create some VBA, using SELECT CASE and the LEFT function to test for T64, T71 etc, concatenate that into an SQL string and use it in a MAKE TABLE query.
Hope this helps,
Anthony
Mon 16 Jul 2012, 10:48Delegate Steven said...
RE: Creating new sheet with values based on custom functions
Hi Anthony,
I am using Excel, i used the code below to find the specific data and some more code to process and create the new sheets. It's probably a long-winded operation but it seems to work without any performance issues.
Thanks for the reply.
Steve
Dim arr As Variant
arr = Array("T64_Boxed_Despatch", "T64_Hanging_Despatch", "T64_Flatpack_Despatch", "T71_Boxed_Despatch", "T71_Hanging_Despatch", "T71_Flatpack_Despatch", _
"T74_Boxed_Despatch", "T74_Hanging_Despatch", "T74_Flatpack_Despatch", "T78_Boxed_Despatch", "T78_Hanging_Despatch", "T78_Flatpack_Despatch", "T86_Boxed_Despatch", _
"T86_Hanging_Despatch", "T86_Flatpack_Despatch", "T88_Boxed_Despatch", "T88_Hanging_Despatch", "T88_Flatpack_Despatch")
Application.ScreenUpdating = False
intTargetRowCount = 2
Dim z As Variant
For intRowCount = 1 To Sheets(strName).Range("A2").CurrentRegion.Rows.Count - 1
z = Filter(arr, Sheets(strName).Range("A2").Cells(intRowCount, 1).Value)
If UBound(z) > -1 Then
Mon 16 Jul 2012, 11:16Trainer Anthony said...
RE: Creating new sheet with values based on custom functions
Hi Steve, I think some of your code got cut off there, but well done for using an array, that should avoid any major performance issues.
Anthony
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Viewing Many WorksheetsIf there are more worksheets in your workbook than there is room to show all their tabs at the bottom of the screen - Right click on the navigation arrows. A list of all your worksheets is shown. You just click on the one that you want to access. If you have more than 15 worksheets, select in the list and choose your worksheet from hundreds. |
