How to update Sheet1 from Sheet2 by Excel VBA ?


 I have one spread sheet with two tabs. 1. Detail tab 2. Report Tab. I would like to Update Report tab from Detail tab after grouping and Date filter by VBA code.

I would like to filter Report Date and match with Date column in Detail tab and compare Department column and group by Status and update Report tab. I did manually for Date 5/1/2015 for demo.

I am attaching my excel file for better understanding. I would like to use some name range or define name so in future if i rename column or move column then code will work.
Share PointAsked:
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.

Glenn RayExcel VBA DeveloperCommented:
Maybe I'm misunderstanding the process, but couldn't you insert a COUNTIFS function to produce those totals instead?

In cell C2 of the Report sheet, insert
and then copy across and down as needed.

This will show the number of occurrences in your Data sheet for Date, Department, and Color category shown.

The advantage of this is that the totals would update automatically as new data is added to the Data sheet.

Share PointAuthor Commented:

 Thanks for quick response. I understand your suggestion but in my scenario i don't want to create column with formula because i would like to update by button whatever Report Date is. Sometime we need to manually update previous week manually for adjust.

So same formula i would like to use by VBA code and i want to update the row which has Report Date match. Can we use same formula in code ? Do we need to change code if we renamed column or Move columns ??

Thank You
Glenn RayExcel VBA DeveloperCommented:
Well, if you intend to manually change the Report data, you won't be able to use any formulas whatsoever.

And yes, if you use VBA to process this and you move columns in your Data sheet, you'll have to revise the code (unlike the COUNTIF formula, which should preserve the criteria ranges).

The following code would update the date-specific data as you request:
Option Explicit
Sub Update_Report()
    Dim rng As Range
    Dim cl As Object
    Dim c As Integer
    Set rng = Range("A2", Range("A2").End(xlDown))
    For Each cl In rng
        If cl.Value = Range("J7").Value Then 'process this date only
            For c = 2 To 4
                cl.Offset(0, c).Value = WorksheetFunction.CountIfs(Sheets("Detail").Range("D:D"), _
                    cl.Value, Sheets("Detail").Range("A:A"), _
                    cl.Offset(0, 1).Value, Sheets("Detail").Range("C:C"), _
                    Cells(1, c + 1).Value)
            Next c
        End If
    Next cl
End Sub

Open in new window

I've attached an example workbook showing this.  Test by removing or replacing some of the existing data and then run the macro (button).


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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Share PointAuthor Commented:
  Thanks a lot . I am still testing. Is it possible to find directly Date and update ? I am worried about more than 7000 rows.  Is it possible we can use Column name for Range instead of A2 or J7 etc ??

Thank You
Glenn RayExcel VBA DeveloperCommented:
1)  Even with 7000 rows, it will process incredibly quickly using the For method here.
2)  You don't want to change the reference to cell "A2" in the code; it presumes the date values are in column A, starting in row 2.  You definitely don't want to refer to the entire column either!
3)  You could change the reference for the report date by assigning a range name to the report date (ex. "RptDate") and then refer to that name in the code (ex., If cl.value = Range("RptDate').value then)

Share PointAuthor Commented:
Thanks Glen. I got it what i wanted..

Thank You !!
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
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.