One Userform For Multiple Sheets

Hi Experts,

I have workbook with 12 sheets in it. Each sheet represents months.

I would like to use UserForm ComboBox to display the data of each sheet.

I have created the ProductCode Combo Box & Monthly Labels, but I don't know how to extract data from multiple sheets.

Once the ProductCode selected in Combo Box, I would like to show its monthly sales numbers in front of respective monthly labels.

I would also like to display Fast Moving Item(Maximum number of sale in all 12 sheets) & Slow Moving Item((Minimum number of sale in all 12 sheets) for the year on this userform.

Please help.

Thank you so much in advance
ConsolidatedReport.xlsm
LVL 31
ShumsDistinguished Expert - 2017Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
This isn't really clear.

Wouldn't it be easier to have a dropdown of months and when a month is selected show a summary of sales for each month.

That summary could be in a 2 column listbox, with the first column for the product code and the second for total quantity sold.
0
ShumsDistinguished Expert - 2017Author Commented:
Hi Norie,

I know that's easiest way to extract data from multiple sheet and sort them in a new sheet.

But I would like to have it in this way? Is it possible?

This UserForm is a part of my previous post...consolidating in one query will loose rewards, so I am trying to get in different post.
0
Martin LissOlder than dirtCommented:
Add

Public ShtName As Worksheet

To the userform' load the form's combobox with the ShtName's data and then show the form.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ShumsDistinguished Expert - 2017Author Commented:
Hi All,

I made it bit easier for you all, I assigned monthly worksheets in user form.

I am getting label.caption in all the months, even that selected product code from combobox.list doesn't exist in few months.

Anyway that was just a try to bring the values in label caption, I want sum of Product code against each month's label.

I also added Fast Moving Item TextBox1 & Slow Moving Item TextBox2, for which I want the Product Code number displayed with maximum sale in all 12 sheets.

Hope I may get a positive response.

Thank you in advance.

Good Night All
ConsolidatedReport.xlsm
0
NorieAnalyst Assistant Commented:
What is it you want exactly?

A summary by month, or a summary by product, or both?
0
ShumsDistinguished Expert - 2017Author Commented:
Good Morning Norie,

I need summary by product.
0
ShumsDistinguished Expert - 2017Author Commented:
Hi Norie,

I found some of your suggestion on below link:
Excel Forum

And so I applied on my requirement, but it does nothing.

Please help.
ConsolidatedReport.xlsm
0
NorieAnalyst Assistant Commented:
Replace the code in the userform module with this exact code.
Option Explicit
Dim wbSales As Workbook
Dim wsInv As Worksheet

Private Sub CommandButton1_Click()
Dim x As Integer
    For x = 14 To 25
        Me.Controls("Label" & x).Caption = ""
    Next x
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()

    Set wbSales = ThisWorkbook
    Set wsInv = wbSales.Worksheets("Master")

    With wsInv
        ComboBox1.List = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value
    End With

End Sub
Private Sub ComboBox1_Change()
Dim wsMonth As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim idx As Long
Dim I As Long

    idx = ComboBox1.ListIndex

    If idx = -1 Then Exit Sub    ' nothing selected

    For I = 1 To 12
        Set wsMonth = Worksheets(MonthName(I, True))
        With wsMonth
            Set rng1 = .Range("C2", .Range("C" & .Rows.Count).End(xlUp))
            Set rng2 = rng1.Offset(, 1)
            Me.Controls("Label" & I + 13).Caption = Application.SumIf(rng1, ComboBox1.List(idx), rng2)
        End With
    Next I

End Sub

Open in new window

0
ShumsDistinguished Expert - 2017Author Commented:
Thanks Norie,

It works perfectly.

I would like to have two more changes:

1. If any product doesn't have its sale for any month, it must show ""(blank) instead of 0.
2. Fast Moving Item & Slow Moving Item yet to display.
0
NorieAnalyst Assistant Commented:
1) That's easy, we can use the Format function to return a blank when CountIf returns 0 - I'll post that later.

2) I haven't tackled this yet because I'm not sure how you want to determine those values.

Would it simply be the highest/lowest seller over the year?

Or are there other factors to take into consideration?
0
ShumsDistinguished Expert - 2017Author Commented:
Hi Norie,

2) Yes, it would be simply the highest/lowest seller over the year, excluding 0 sales.
0
NorieAnalyst Assistant Commented:
Here's the workbook with 1) done, still working on 2) which isn't  straightforward.
ConsolidatedReport-V2.xlsm
0
ShumsDistinguished Expert - 2017Author Commented:
Thanks Norie,

Working Perfect...

Waiting for 2) desperately.
0
ShumsDistinguished Expert - 2017Author Commented:
Good Day Norie,

Hope you are doing well....

I have few suggestion, I was looking through Consolidate Data Option.

1. We can create a "Report" ws after the last sheet which is Dec, then run a consolidate source Array through Jan till Dec ws.
2. AutoFilter Qnty Column from High to Low
3. Which ever Product Code on B2, will refer as Fast Moving Item.
4. Which ever Product Code in last row of B column, will refer as Slow Moving Item.
5. Then a Label Caption against them will give us the quantity sold.

This would be simplest way, please advice.

I have attached a sample workbook.
ConsolidatedReport-V2.xlsm
0
NorieAnalyst Assistant Commented:
The problem is that there are multiple products, 32 in fact that have quantity of 1 over the whole year.

So which one is the slow mover?

I've actually got code to find the fast mover and in the sample date there's only ony one fast mover, but what if there was more than one?
0
ShumsDistinguished Expert - 2017Author Commented:
Hi Norie,

I would prefer to be first max as Fast Moving and first Min as Slow Moving. We don't need to list all.
0
NorieAnalyst Assistant Commented:
What do you mean by first?

Do you want to take the months  into account?
0
ShumsDistinguished Expert - 2017Author Commented:
first maximum and first minimum in that column.

No, this would be over the year, I don't want monthly basis.
0
NorieAnalyst Assistant Commented:
That means that the fast/slow movers will always come from January.
0
ShumsDistinguished Expert - 2017Author Commented:
no Norie,

Fast/Slow numbers will be the sum of items sold over the year.
0
NorieAnalyst Assistant Commented:
Sorry but I don't think you are going about this the right way.

Surely if you want to a summary you want to see it product by month not by year.
0
ShumsDistinguished Expert - 2017Author Commented:
Sorry Norie,

For so much confusion, will you able to provide VBA code for Consolidate options as advised earlier, I think we can get that as I m planning.
0
ShumsDistinguished Expert - 2017Author Commented:
Norie,

Check this query;
Sales & Inventory Management System

If you see in this, we can get the result from Sales Column, which ever is highest will be Fast Moving and which ever is just "1" sale will be Slow Moving, if there was more than one, then it must pick the first entry from same column.
0
NorieAnalyst Assistant Commented:
I can consolidate the date from all 12 months, in fact I've already coded that when trying to find the fastest mover.

Though the code didn't actually write the 'results' to a sheet, but that can easily be remedied.

I'll have a look at the latest file you attached.
0
Ken ButtersCommented:
rewrote all code on this sheet in order to combine with Sales & Inventory Management System

Added slow/fast moving as well.
SalesRegister.xlsm
0
ShumsDistinguished Expert - 2017Author Commented:
Thanks again Buttersk,

Two changes I would like to have:

1. I would like to calculate Slow Moving only if its >= 1. Exclude 0 Sales.
2. Slow/Fast Moving Quantity in Label30 & 31.
0
ShumsDistinguished Expert - 2017Author Commented:
I would think, what if we create a command button for both Fast & Slow Moving Products, after clicks it must display top 10 product with their sales count for fast moving and last 10 products with their sales count for slow moving and it must be >=1.

Just a suggestion. Please advice.
0
Ken ButtersCommented:
your top 10 is perfectly doable... and sounds like a great start to a new question! :)

You would need to outline what you want to do if you don't have enough products for a top 10 or a bottom 10.
0
ShumsDistinguished Expert - 2017Author Commented:
:) I still believe, you can surely do it :)
0
Ken ButtersCommented:
1. I would like to calculate Slow Moving only if its >= 1. Exclude 0 Sales. [COMPLETE]
2. Slow/Fast Moving Quantity in Label30 & 31. [COMPLETE]

Note... I renamed all the labels / controls to be more obvious in the code as to what they are.
SalesRegister.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsDistinguished Expert - 2017Author Commented:
Speechless.....Extremely perfect.....

Thanks a million......

Hope to see you soon......I know you don't wish the same :), but I am very glad to work with you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.