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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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