dynamically generated list

bogusnews

New Member
Joined
Mar 17, 2019
Messages
6
I run a window cleaning company and use Excel for accounting. I have a page that has all my customers. There is a column that has the month which shows the month their windows are due to be cleaned again.
On my dashboard, I want to show all the customers due to have their windows cleaned for this month.
It's really easy to show one of them using Vlookup... but I want a list of them all dynamically generated on my dashboard.
So I want to have the company name, contact, phone, email and amount shown.
Best way of doing this? All the information I need is available on one page.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

bogusnews

New Member
Joined
Mar 17, 2019
Messages
6
Sure. More than happy to do that. I'm just going out... will put something up later. Will show a screeshot of the contact screen and what i want for the dashboard.

Thanks!
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,803
Office Version
  1. 2013
Platform
  1. Windows
I see your new to the Forum.

It's always best when asking for help to provide specific details.

We need to know all the sheet names that pertain to this question
We need all the column numbers pertaining to this question.

Lets assume your DashBoard is named "Master"

And the sheet you want to copy from is named "Contracts"

And lets assume the Date will be in column "G" of sheet named "Contracts"

And lets assume we need to copy all data from column A to G

Then try using this script.

Or modify script to your needs or provide specific details.

Code:
Sub This_Months_Work()
'Modified  3/18/2019  3:33:04 AM  EDT
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim ans As Long
ans = Month(Date)
Sheets("Master").Activate
Dim Lastrow As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
Dim Lastrowa As Long
Lastrowa = Sheets("Contracts").Cells(Rows.Count, "G").End(xlUp).Row
For i = 2 To Lastrowa
    With Sheets("Contracts")
        If Month(.Cells(i, "G").Value) = ans Then
            .Cells(i, 1).Resize(, 8).Copy Sheets("Master").Cells(Lastrow, 1)
            Lastrow = Lastrow + 1
        End If
    End With
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "Some cell in column G is not a date"
Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,683
Messages
5,524,265
Members
409,566
Latest member
MickB

This Week's Hot Topics

Top