VBA to split data and apply a percentage sum to total values

Morning,

I have the following code to do the following;

Copy orignal data to a workbook
Filter by a reference 'A214'
Copy each entry of A214 and reference A200, A210 & A220.

What i need to do now is identify the percentage (user input on sheet called 'Start') and apply a sum of the (A214) value * the percentage on the start sheet to give me a split amount for A200, A210 & A220, this will then tie back to the orignal A214 values?

Hope this makes sense,

Code below;

Please Login or Register  to view this content.

I have attached sample data, a quick repsonse would be greatly appreciated, i need to get this project out by today.

So first you want a sum in the original data tab right for all the numerical values?

I had provided you the code for the sum in the other request. Didnt it work for you?

So the subtotals created by the macro should be replaced by values which are a %age of the original value?

Or

Should the values shown in the various columns like V, AC, AD etc be changed to be a % of the original value?

Ok, got it (i think )

Can you list out the columns that need to be changed?

One more question - is there any unique identifier in the data in the tabs A200, A210 and A220 that ties back to A214?

What if the data in A214 gets sorted (by mistake) and no longer matches with the data in the subsequent tabs, how do we ensure that the % applied are correct?

So you will be running my earlier part of the code which separates the data and then creates subtotals for each tab, right?

Or will the subtotals be put after the code that i will give you, will be run?

So the subtotals code will not be used but will need to be changed to a totals row?

This is the updated code

Please Login or Register  to view this content.

Do you want the totals for the same columns listed in post 8?

Do you want the totals for original data and the 3 tabs?

So all numerical columns not just the ones you have listed out?

What error are you getting?

Actually, this code has been developed as a consolidated effort in 2-3 threads. So each time, you might have provided varying sheet names, hence the confusion.

You can let me know how your final workbook will look like and i can help you to correct the code.

You mean you are seeing the text "Cell D6" and "Cell D9" in the Start page?

I ran your code in the sample file you uploaded and it works fine.

I guess the confusion lies in the varying sheet names. Once you give me the info as of post 19, even this will fall in place.

What is the error you received after you ran the code?

Will this format
Start
Original Data
A200
A210
A220

be the final format?

What is the error you received after you ran the code?

Originally Posted by kenadams378

Please Login or Register  to view this content.

I now have an error message with this code, saying there is a lack of resources

Looks like its huge data that is being copied. Will devise an alternate code for this portion.

Did the values populate after calculating the % of the total?

Try this code and let me know if it poses any errors -

Please Login or Register  to view this content.

You had mentioned that you wanted to filter the data and move it to original (filtered). Do you want to still do that?

If yes, we need to create a sheet called original(filtered) and then the code will work fine.

Try this

Please Login or Register  to view this content.

This is weird.

Does your Start sheet have A200 in A3, A210 in A4 and A220 in A5?

Do the sheet names match with the above 3?

I ran the code at my end and it worked fine.

Hmmm thats what caused the error. Your sample file had the data how i had mentioned.

Change this line from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

When you get the error and see the above row highlighted in yellow, can you hover your mouse onto sname and tell me what value you see?

Same for lrow.

No Kyle.

I have already qualified the row much earlier in the code. And it cannot be worksheets("Sheet1") in this case.

OH yeah, thanks Kyle.

Kenadams,

Change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The whole code is in post 31. I have qualified it there just below

Please Login or Register  to view this content.

hence i didnt qualify it again.

If you remove the "." before the Rows, what error are you getting?

When you get the error, whats the value for sname and lrow when you hover the mouse over it?

Ok, i get it.

Ken,

Try this final code -

Please Login or Register  to view this content.

Note: I ran the macro when it was not qualified and it ran fine. I wonder why it fluctuates like this.

Oops sorry i have edited the code in post 53.

Let me run the macro (after changes) on your original file and attach it here. Maybe you can have a look and see.

---------- Post added at 04:55 PM ---------- Previous post was at 04:51 PM ----------

There was a slight error in the above code. It was missing the Step 3 in for i = 6 to 12.

Now you should be able to run it without any issues. If not, i will run it at my end.

Let me explain this part of the code to you.

Please Login or Register  to view this content.

Pcent takes the value of cell B6 from the Start sheet and sname takes the value from A6.

Then the code moves down 3 rows and takes the values from A9 & B9 and then A12 & B12.

When you hover your mouse over sname, what value is it showing?

Can you attach your file? I have made some more modifications to the code in post 53. Also i ran the macro at my end and attaching it here.

Right click on module and remove it. Module 2 is the right one.

Also in my code the CHDir statement has a ' in front of it. Remove that '.

The sname value cant be "". This is what is causing the error.

---------- Post added at 05:45 PM ---------- Previous post was at 05:37 PM ----------

Oops i get it. In your start page, do you have the sheet names like A200, A210, etc in column B and the % in column C? As per your original attachment, it was in column A & B not B & C.

So this code should be changed from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

In both your file and mine.

You have not run the updated code.

I had changed it. I guess this thread is very long so its creating confusion.

Updated code here

Please Login or Register  to view this content.

This code considers that Sheet names A200, A210 are in column B and % in column C.

Did you try running it in my workbook? I ran the code again and it works fine.

---------- Post added at 06:10 PM ---------- Previous post was at 06:06 PM ----------

I added some code lines to make your code run faster.

Please Login or Register  to view this content.