Link to home
Start Free TrialLog in
Avatar of Shums Faruk
Shums FarukFlag for India

asked on

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
Avatar of Norie
Norie

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.
Avatar of Shums Faruk

ASKER

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

Public ShtName As Worksheet

To the userform' load the form's combobox with the ShtName's data and then show the form.
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
What is it you want exactly?

A summary by month, or a summary by product, or both?
Good Morning Norie,

I need summary by product.
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
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

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.
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?
Hi Norie,

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

Working Perfect...

Waiting for 2) desperately.
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
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?
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.
What do you mean by first?

Do you want to take the months  into account?
first maximum and first minimum in that column.

No, this would be over the year, I don't want monthly basis.
That means that the fast/slow movers will always come from January.
no Norie,

Fast/Slow numbers will be the sum of items sold over the year.
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.
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.
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.
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.
rewrote all code on this sheet in order to combine with Sales & Inventory Management System

Added slow/fast moving as well.
SalesRegister.xlsm
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.
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.
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.
:) I still believe, you can surely do it :)
ASKER CERTIFIED SOLUTION
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.