Creating new sheet with values based on custom functions

Resolved · High Priority · Version 2007

Wed 11 Jul 2012, 09:43 Delegate 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:34 Trainer 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:48 Delegate 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:16 Trainer 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 courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Viewing Many Worksheets

If 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.

View all Excel hints and tips