creating new sheet values

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

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

resolvedResolved · High Priority · Version 2007

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

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

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

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

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

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


Server loaded in 0.06 secs.