Creating Pivot / Consult
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Creating Pivot / Consult
I have a master report, that I am importing data sheets to as needed. Thanks Hans. Now my dilemma is what to do with the data.... The fact is I have a deadline on this and way too many ideas.
It's intent. Display site specific data in a dashboard like format. I would like it to have a user form interface, which displays the numeric metrics. While on the sheets, those "numeric metrics", populate pivot tables and charts.
I think to start and provide a specific question. On sheet "Main" column (B) I need the names of the imported sheets (Site id's) in order to get the row source for the list in userform1.
How can I code vb to create a pivot table in sheet "Dashboard" based on the listbox selected site id (which is also the sheetname) in UserForm1? The range (a1:aa:5) The row labels I would need are:
Gross Revenue
Net Revenue
Actual NRPMH
Productivity
The column I'd like "Date" but only the last 30 days.
That should get me started, I can do most of this on my own I just need a jump start!!! Any ideas are welcome, please.
I attached a sample of the workbook, with dummy data in it.
It's intent. Display site specific data in a dashboard like format. I would like it to have a user form interface, which displays the numeric metrics. While on the sheets, those "numeric metrics", populate pivot tables and charts.
I think to start and provide a specific question. On sheet "Main" column (B) I need the names of the imported sheets (Site id's) in order to get the row source for the list in userform1.
How can I code vb to create a pivot table in sheet "Dashboard" based on the listbox selected site id (which is also the sheetname) in UserForm1? The range (a1:aa:5) The row labels I would need are:
Gross Revenue
Net Revenue
Actual NRPMH
Productivity
The column I'd like "Date" but only the last 30 days.
That should get me started, I can do most of this on my own I just need a jump start!!! Any ideas are welcome, please.
I attached a sample of the workbook, with dummy data in it.
Last edited by Joseph on 02 Feb 2011, 22:02, edited 1 time in total.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating Pivot / Consult
I'd use dynamic named ranges (created in the Name Manager in the Formulas tab of the ribbon) to populate the list boxes:
Select_Site refers to =OFFSET(Main!$A$3,0,0,COUNTA(Main!$A:$A)-2,1)
Use as Row Source for List_Sites on Mainform.
Sites refers to =OFFSET(Main!$B$2,0,0,COUNTA(Main!$B:$B)-1,1)
Use as Row Source for ListBox1 on UserForm1.
You could add the name of each imported sheet to column B on the Main sheet:
Not sure what you want with the pivot table. You mention 4 row labels but they are number fields which I would expect in the value area, and you don't mention a value field.
Select_Site refers to =OFFSET(Main!$A$3,0,0,COUNTA(Main!$A:$A)-2,1)
Use as Row Source for List_Sites on Mainform.
Sites refers to =OFFSET(Main!$B$2,0,0,COUNTA(Main!$B:$B)-1,1)
Use as Row Source for ListBox1 on UserForm1.
You could add the name of each imported sheet to column B on the Main sheet:
Code: Select all
Dim r As Long
With Worksheets("Main")
r = .Range("B" & .Rows.Count).End(xlUp).Row + 1
.Range("B" & r) = strSiteID
End With
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Creating Pivot / Consult
Ok Hans, I have the sheet naming and the Named Ranges set. Thanks.
Im trying now to get 2 multi select boxes, and I can't figure it out. Here is what I have:
The form has 2 multiselect listboxes (List_sites2 ColumnD) & (Listbox3 ColumnG) and 1 Calendar (Calendar1 ColumnE). All starting in Row2.
Need them to input the sections in unison. I'm just not sure how to get 2 listboxes to loop through together.
Im trying now to get 2 multi select boxes, and I can't figure it out. Here is what I have:
Code: Select all
Dim ws As Worksheet
Set ws = Worksheets("Main")
'find first empty row in database
iMaxRow = ws.Cells(Rows.Count, 4).End(xlUp).Row
iRow = iMaxRow
datDate = Me.Calendar1.Value
strMet = Me.ListBox3.List(i)
For i = 0 To Me.List_sites2.ListCount - 1
If Me.List_sites2.Selected(i) Then
strSel = Me.List_sites2.List(i)
For r = 1 To iMaxRow
If ws.Cells(r, 4).Value = strSel And ws.Cells(r, 5).Value = datDate Then
Exit For
End If
Next r
If r > iMaxRow Then
iRow = iRow + 1
r = iRow
End If
ws.Cells(r, 4).Value = strSel
ws.Cells(r, 5).Value = datDate
End If
Next i
'Here is where the problem is, need this listbox to follow suit it currently enters the top value only, and just to one record.
For n = 0 To Me.ListBox3.ListCount - 1
If Me.ListBox3.Selected(i) Then
strMet = Me.ListBox3.List(i)
End If
ws.Cells(r, 7).Value = strMet
Next n
Need them to input the sections in unison. I'm just not sure how to get 2 listboxes to loop through together.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating Pivot / Consult
Your code is inconsistent. You have
For n = 0 To Me.ListBox3.ListCount - 1
which makes n the loop index that will be 0, 1, 2 etc., but then you have
If Me.ListBox3.Selected(i) Then
which uses a variable i that doesn't have anything to do with the loop index n.
Moreover, in the lines
you use strMet regardless of whether the list item was selected or not, because ws.Cells(r, 7).Value = strMet comes AFTER the End If. And it is not clear what r is here - you have used it in a loop higher up in the thread but that loop has already ended.
You have to make your code consistent and logical.
Something that will help is indenting the code consistently. It is currently difficult to follow because the indenting is haphazard.
For example, if you have a line If ... Then, the corresponding End If should have the same left indent, and the lines in between If ... Then and End If should be indented further.
If you format your code
like this:
it becomes somewhat easier to follow because you can see at a glance what belongs to what.
For n = 0 To Me.ListBox3.ListCount - 1
which makes n the loop index that will be 0, 1, 2 etc., but then you have
If Me.ListBox3.Selected(i) Then
which uses a variable i that doesn't have anything to do with the loop index n.
Moreover, in the lines
Code: Select all
If Me.ListBox3.Selected(i) Then
strMet = Me.ListBox3.List(i)
End If
ws.Cells(r, 7).Value = strMet
You have to make your code consistent and logical.
Something that will help is indenting the code consistently. It is currently difficult to follow because the indenting is haphazard.
For example, if you have a line If ... Then, the corresponding End If should have the same left indent, and the lines in between If ... Then and End If should be indented further.
If you format your code
Code: Select all
For i = 0 To Me.List_sites2.ListCount - 1
If Me.List_sites2.Selected(i) Then
strSel = Me.List_sites2.List(i)
For r = 1 To iMaxRow
If ws.Cells(r, 4).Value = strSel And ws.Cells(r, 5).Value = datDate Then
Exit For
End If
Next r
If r > iMaxRow Then
iRow = iRow + 1
r = iRow
End If
ws.Cells(r, 4).Value = strSel
ws.Cells(r, 5).Value = datDate
End If
Next i
Code: Select all
For i = 0 To Me.List_sites2.ListCount - 1
If Me.List_sites2.Selected(i) Then
strSel = Me.List_sites2.List(i)
For r = 1 To iMaxRow
If ws.Cells(r, 4).Value = strSel And ws.Cells(r, 5).Value = datDate Then
Exit For
End If
Next r
If r > iMaxRow Then
iRow = iRow + 1
r = iRow
End If
ws.Cells(r, 4).Value = strSel
ws.Cells(r, 5).Value = datDate
End If
Next i
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Creating Pivot / Consult
I do apologize for your having to decipher that abortion. I fixed up what I had, and got the second listbox working the way I want, however the code seems redundant. Is there an easier/better way, or is this ok?
Code: Select all
Dim iRow As Long
Dim iMaxRow As Long
Dim r As Long
Dim strSel As String
Dim strMet As String
Dim datFrom As Long
Dim datTo As Date
Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Main")
'Site Id List box from date and to date
iMaxRow = ws.Cells(Rows.Count, 4).End(xlUp).Row
iRow = iMaxRow
datFrom = Me.Calendar1.Value
datTo = Me.Calendar2.Value
For i = 0 To Me.List_sites2.ListCount - 1
If Me.List_sites2.Selected(i) Then
strSel = Me.List_sites2.List(i)
For r = 1 To iMaxRow
If ws.Cells(r, 4).Value = strSel And ws.Cells(r, 5).Value = datFrom Then
Exit For
End If
Next r
If r > iMaxRow Then
iRow = iRow + 1
r = iRow
End If
ws.Cells(r, 4).Value = strSel
ws.Cells(r, 5).Value = datFrom
ws.Cells(r, 6).Value = datTo
End If
Next i
'Metric list box
iMaxRow = ws.Cells(Rows.Count, 7).End(xlUp).Row
iRow = iMaxRow
strMet = Me.ListBox3.List(i)
For i = 0 To Me.ListBox3.ListCount - 1
If Me.ListBox3.Selected(i) Then
strMet = Me.ListBox3.List(i)
For r = 1 To iMaxRow
If ws.Cells(r, 7).Value = strMet Then
Exit For
End If
Next r
If r > iMaxRow Then
iRow = iRow + 1
r = iRow
End If
ws.Cells(r, 7).Value = strMet
End If
Next i
End Sub
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating Pivot / Consult
That looks mostly OK, but there are indeed a few redundant lines.
Code: Select all
Dim iMaxRow As Long
Dim r As Long
Dim strSel As String
Dim strMet As String
Dim datFrom As Long
Dim datTo As Date
Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Main")
'Site Id List box from date and to date
iMaxRow = ws.Cells(Rows.Count, 4).End(xlUp).Row
datFrom = Me.Calendar1.Value
datTo = Me.Calendar2.Value
For i = 0 To Me.List_sites2.ListCount - 1
If Me.List_sites2.Selected(i) Then
strSel = Me.List_sites2.List(i)
For r = 1 To iMaxRow
If ws.Cells(r, 4).Value = strSel And ws.Cells(r, 5).Value = datFrom Then
Exit For
End If
Next r
ws.Cells(r, 4).Value = strSel
ws.Cells(r, 5).Value = datFrom
ws.Cells(r, 6).Value = datTo
End If
Next i
'Metric list box
iMaxRow = ws.Cells(Rows.Count, 7).End(xlUp).Row
For i = 0 To Me.ListBox3.ListCount - 1
If Me.ListBox3.Selected(i) Then
strMet = Me.ListBox3.List(i)
For r = 1 To iMaxRow
If ws.Cells(r, 7).Value = strMet Then
Exit For
End If
Next r
ws.Cells(r, 7).Value = strMet
End If
Next i
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Creating Pivot / Consult
Ok great. I have everything working well, but am now stuck.
If you would please, take a look at userform1, it creates a kind of look up table on the "Dashboard" sheet based on the selections. I've managed to also select the data sheets needed. What I want to do is this.
Gather an example for the finding and summing, between the chosen date range and Site Id, of the first metric "Gross Revenue".
The items in Listbox3 can all be found on row1 of the strSiteId "UN_DAY" worksheet.
Activate sheet from List_Sites2 - (This is where the code ends in the example wrkbk)
Find date range in column A from Calendar1 and Calendar2
Find Metric column by Listbox3
Sum values
Return total Gross Revenue to Dashboard sheet target
Row - List_sites2 row
Column - Listbox3 column
I plan to spend time making this thing pretty at a later date.
If you would please, take a look at userform1, it creates a kind of look up table on the "Dashboard" sheet based on the selections. I've managed to also select the data sheets needed. What I want to do is this.
Gather an example for the finding and summing, between the chosen date range and Site Id, of the first metric "Gross Revenue".
The items in Listbox3 can all be found on row1 of the strSiteId "UN_DAY" worksheet.
Activate sheet from List_Sites2 - (This is where the code ends in the example wrkbk)
Find date range in column A from Calendar1 and Calendar2
Find Metric column by Listbox3
Sum values
Return total Gross Revenue to Dashboard sheet target
Row - List_sites2 row
Column - Listbox3 column
I plan to spend time making this thing pretty at a later date.
Last edited by Joseph on 03 Feb 2011, 00:14, edited 1 time in total.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating Pivot / Consult
Not sure this'll be easy to do, but a few remarks:
1) You don't require explicit declaration of variables. Please see The importance of 'Option Explicit'.
2) As noted before, inconsistent indentation makes code hard to read.
3) In many situations, it is not necessary to select cells to manipulate them. Code usually runs faster if you don't.
For example, the following lines
Worksheets("A").Select
Range("A1:A4").Select
Selection.Copy
Worksheets("B").Select
Range("B1").Select
Selection.Paste
can be replaced by a single line:
Worksheets("A").Range("A1:A4").Copy Destination:=Worksheets("B").Range("B1")
4) Specific to your problem: you mention Gross Revenue. Is this just an example, or should Gross Revenue always be included even if it is not selected in the Metric list box?
1) You don't require explicit declaration of variables. Please see The importance of 'Option Explicit'.
2) As noted before, inconsistent indentation makes code hard to read.
3) In many situations, it is not necessary to select cells to manipulate them. Code usually runs faster if you don't.
For example, the following lines
Worksheets("A").Select
Range("A1:A4").Select
Selection.Copy
Worksheets("B").Select
Range("B1").Select
Selection.Paste
can be replaced by a single line:
Worksheets("A").Range("A1:A4").Copy Destination:=Worksheets("B").Range("B1")
4) Specific to your problem: you mention Gross Revenue. Is this just an example, or should Gross Revenue always be included even if it is not selected in the Metric list box?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Creating Pivot / Consult
Ah ha..thanks for the tips!!
This was intended as an example.
Gross Revenue will only be included if it is selected. I hope I can then learn from the example and fill in the rest.
This was intended as an example.
Gross Revenue will only be included if it is selected. I hope I can then learn from the example and fill in the rest.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating Pivot / Consult
It's not clear to me yet what should happen if the workbook contains sheets for several sites and if the user selects multiple sites in the list box on the userform. Do you want to add the data for the sites together, or should they be grouped by site, or ...?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Creating Pivot / Consult
That Option Explicit thread is key, I had no idea.
There will be more than just the UN_DAY option and sheet. If more than one site is selected, they must be treated individually.
There will be more than just the UN_DAY option and sheet. If more than one site is selected, they must be treated individually.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating Pivot / Consult
Sorry to keep asking so many questions. What exactly do you mean by that? Do you want a pivot table for each, or ...?Joseph wrote:If more than one site is selected, they must be treated individually.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Creating Pivot / Consult
This Wbk I added another dummy data sheet and Site Id.
The first site selection, goes to Row6 on the Dashboard sheet. The next selection, right below in Row7.
So in this example the "Gross Revenue" for UN_DAY would go to B6, and the "Gross Revenue" for UN_CHF would go to B7.
List_sites2 should:
Find data source page -StrSiteId
And
Find the destination Row in sheets "Dashboard" -StrSiteId
While
Listbox3 finds the Column source in StrSiteId sheet
And
Find destination Column in Dashboard
Calendar1 and Calendar2 define the row source in StrSiteId
Hope this helps..I'm trying to get better in my descriptions...
The first site selection, goes to Row6 on the Dashboard sheet. The next selection, right below in Row7.
So in this example the "Gross Revenue" for UN_DAY would go to B6, and the "Gross Revenue" for UN_CHF would go to B7.
List_sites2 should:
Find data source page -StrSiteId
And
Find the destination Row in sheets "Dashboard" -StrSiteId
While
Listbox3 finds the Column source in StrSiteId sheet
And
Find destination Column in Dashboard
Calendar1 and Calendar2 define the row source in StrSiteId
Hope this helps..I'm trying to get better in my descriptions...
Last edited by Joseph on 03 Feb 2011, 22:45, edited 1 time in total.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating Pivot / Consult
I'm sorry, I looked at it but I can't help you with this.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Creating Pivot / Consult
I completely understand, this was a bit of a loaded question to say the least. I was perhaps making this more complicated than it needs to be, so I'm taking an easier direction, though requires much tediousness.
I know how to find a row with this line:
irow = ws.Columns("A:A").Find(DateValue(Me.datefrom.Text)).Row
How can it be changed to find a column within row1 with (Me.Met1.text)? And how can the two lines be used together to determine a target cell?
I know how to find a row with this line:
irow = ws.Columns("A:A").Find(DateValue(Me.datefrom.Text)).Row
How can it be changed to find a column within row1 with (Me.Met1.text)? And how can the two lines be used together to determine a target cell?
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating Pivot / Consult
Perhaps something like
irow = ws.Columns("A:A").Find(DateValue(Me.datefrom.Text)).Row
icol = ws.Rows(irow).Find(Me.Met1.Text).Column
The cell you're looking for is ws.Cells(irow, icol)
irow = ws.Columns("A:A").Find(DateValue(Me.datefrom.Text)).Row
icol = ws.Rows(irow).Find(Me.Met1.Text).Column
The cell you're looking for is ws.Cells(irow, icol)
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Creating Pivot / Consult
Got it, big helper.
Though, I'm getting an Error 91.
irow = Worksheets("" & strSiteID & "").Columns("A:A").Find(DateValue(Me.datefrom.Text)).Row
icol = Worksheets("" & strSiteID & "").Rows(1).Find(Me.met1.Text).Column
Worksheets("" & strSiteID & "").Cells(irow, icol).Select
I tried using Set, no luck.
Though, I'm getting an Error 91.
irow = Worksheets("" & strSiteID & "").Columns("A:A").Find(DateValue(Me.datefrom.Text)).Row
icol = Worksheets("" & strSiteID & "").Rows(1).Find(Me.met1.Text).Column
Worksheets("" & strSiteID & "").Cells(irow, icol).Select
I tried using Set, no luck.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: Creating Pivot / Consult
On the irow or icol.
Sorry I don't have an equals key on my phone. Son is on my pc.
I have
dim irow as long
Dim icol as long
Sorry I don't have an equals key on my phone. Son is on my pc.
I have
dim irow as long
Dim icol as long