VBA help please

IEvansNH

New Member
Joined
Mar 28, 2013
Messages
12
Hello all,

As my first post, I'm looking for some help creating a macro for a summary page in a workbook. I did some searches, but I don't think the wording of my inquiry was effective.

Here's the jist; I have a 5 tab worksheet where sheets 2-5 have a lot of data, but only some of the data is needed for the summary. Column A has names, column C has a product name and column K has data that, if the value is greater than one, along with the product name and their name, need to be carried to specific columns in the summary worksheet. The intent is to have all of the information in sheets 2-5 updated weekly and once pasted, the macro would update the specifics automatically.

I'm a newb, but have a keen interest in developing my skills here. Hope someone can guide me a bit.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi IR,

Welcome to the forum.

Lets start here.

Say on sheet 2 row 6 if cell K6 is greater than 1 you want the data from A6, C6 and K6 to be transfered to sheet 1 which is the summary sheet?
If that is true, where on summary sheet do you want that info? In columns A, C & K? Or A, B, C?

Regards,
Howard
 
Upvote 0
L. Howard, thank you for your response. You have my thought process firmly in hand.

When the values you stated are true, I would like to have the information from A6 fall into column A of sheet 1 and then K6 would end up in either D6(Product A) or E6(Product B) of sheet 1. Because sheet 2 has 2 different products in C6 that could be represented by the IfThen equation, I would like the actual value in K6 to end up in the corresponding column.

I hope I'm being clear in my verbalization and thank you for your help.

Ian
 
Upvote 0
Ian,

... and then K6 would end up in either D6(Product A) or E6(Product B) of sheet 1.

Is this a typo above? Did you mean C6 has two different products?

Here you say K.value of sheet 2 goes to K of sheet 1

... I would like the actual value in K6 to end up in the corresponding column.

I am presently working eith the assumption that C of sheet 2 having two different products and putting product A into D of sheet 1 and product B into E of sheet 1.

Howard
 
Upvote 0
Ian,

Take a look here. Am I on the right track?

Basically I got four sheets with a bit of phony data to simulate what I think you are dealing with. Some changes in the code will be necessary for it to work on your real life stuff.

Parse through the data sheets and get an idea of what you expect to show up on sheet 1. For example, look at column K of sheet 2 and find the first value greater than 1, note the data to the left in that row and see if it indeed does go to sheet 1 columns as you want.

Change some values in any of the K columns and see if the genie in the bottle obeys your wishes over on sheet 1.

Run the code with Key stroke "ctrl + a" and check results on sheet 1.
Clear the test data on sheet 1 with key stroke "ctrl + e".

More efficient code is surely possible if a pro were offering up his expertise. If this gets too slow, we can throw it to wolves on the forum for improvements. Perhaps a few will give it a look on their own and wave their majic wands over it and work wonders.

https://www.dropbox.com/s/916qonw6u18mima/IEvansNH_Drop_Box_Example.xlsm

Regards,
Howard
 
Upvote 0
Howard, this has the makings of a beautiful thing. First glance has things on the right track. Column A has the names, which is correct. Column's D and E show signs of correct separation by product and as I view your macro in action, I realize i don't need representation on the summary sheet in column K. This what I am hoping my summary page will eventually look like

https://www.dropbox.com/s/09gmkmozhxwa60h/Product Summary.xlsx
 
Upvote 0
To move forward I will need you to add a sheet2 and provide 5 to 10 rows in it of example data in the exact sheet lay out-format as your real life working sheet.

Looks like instead of splitting one set of two products there are four major catagories with eleven products to split out. Could get sticky but will need to see the entire layout of the source sheet before I panic.<g>

And the ZIP code is in there somewhere too.

Howard
 
Upvote 0
Is column K of sheet 2 & 3 still the trigger to affect a transfer to sheet 1, that is, if K is greater than 1 then transfer?

Products A to E are shown in 2 & 3, will F to K exist in real life?

Remind me again how many transfer FROM sheets will there be.

The blue shaded columns are the target to transfer, right?

Howard
 
Upvote 0
Column K is the trigger. Products F-K will exist and the master lists will be on tabs 4(F-H) and 5(I-K), so four transfer sheets total. Yes, the blue columns are info to be placed on the summary page. For each transfer sheet, many, if not all of the names will be the same.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top