How do I update my Excel list with new rows automatically ?

Hi Experts,

I have a workbook which holds Product information and YTD values of products sold.

The 'Master' sheet holds the complete Product list at the start of the year and is made up of the following columns: Product ID, Product Description, YTD Target amount, YTD Actual amount.

This sheet is updated each month with the latest YTD figures for those products which have sold this year. So, for example, with 1000 products, only 600 of those products may have actually been sold to date.

The YTD updates are entered on a sheet called 'YTD April' (for example), and the Master figures are updated automatically using VLOOKUP.

This works fine except when a new product has been introduced and entered on the YTD April sheet. Is it possible to update the Master sheet with new product rows automatically, rather than manually ?

Thanks
Toco
TocogroupAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Roy CoxGroup Finance ManagerCommented:
Do you mean that each sheet requires matching Rows?

Is the data stored in Excel Tables?
0
TocogroupAuthor Commented:
I've attached a simplified version of the workbook.

The Master sheet is created at the beginning of the year when all known products are their target values are entered. This sheet is updated each month from the YTD sheet (YTD April in the example) with the latest YTD amounts for each product.

In the example I've attached, you will see there are products listed on the Master sheet which haven't actually sold to date (eg 5ST003).

The problem arises if a new product is sold and appears on the YTD April sheet (highlighted in red in the example). I want it to automatically appear on the Master sheet without me having to manually copy and paste.

No, the data isn't stored in Excel Tables.
Product-YTD-Example-workbook.xlsx
0
Martin LissOlder than dirtCommented:
Here's a macro you can use. It assumes that the new YTD sheet is active. In case you need them, here's how you add the macro.

In Excel, Press Alt+F11 to open Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu

Copy the macro and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

Optionally, press Alt+F8 to open the "Macro" dialog window. Select the macro, click ‘Options…’,  hold down the Shift key and type the letter A (or any other letter) and click ‘OK’.  Then anytime you want to run the macro press Ctrrl+Shift+A

Sub UpdateMaster()

Dim lngLastRow As Long
Dim lngNewRow As Long
Dim lngRow As Long
Dim rngFound As Range
Dim intCount As Integer

Application.ScreenUpdating = False

If InStr(ActiveSheet.Name, "YTD") = 0 Then
    MsgBox "Please go to the current YTD sheet and try again"
    Exit Sub
End If

lngLastRow = Range("A1048576").End(xlUp).Row

With Sheets("Master").UsedRange
    For lngRow = 2 To lngLastRow
        Set rngFound = .Find(What:=ActiveSheet.Cells(lngRow, 1), LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
        If rngFound Is Nothing Then
            With Sheets("Master")
                lngNewRow = .Range("A1048576").End(xlUp).Row + 1
                .Cells(lngNewRow, 1) = ActiveSheet.Cells(lngRow, 1)
                .Cells(lngNewRow, 2) = ActiveSheet.Cells(lngRow, 2)
                .Cells(lngNewRow, 4) = ActiveSheet.Cells(lngRow, 3)
                intCount = intCount + 1
            End With
        End If
    Next
End With

MsgBox intCount & " products added to Master from sheet " & ActiveSheet.Name
Application.ScreenUpdating = True
End Sub

Open in new window

0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Martin LissOlder than dirtCommented:
I could improve the selection of the source sheet if I knew more about the structure of your workbook. So...

1. Next month will there be both YTD April and YTD May sheets? In other words will there ever be more than one YTD sheet?
2. Do other sheets besides the Master and the YTD sheet(s) exist?
0
TocogroupAuthor Commented:
Hi Martin,

Thanks for your solution. I've just been going through your macro and it does pretty much as I want it to. Although, the Master will need sorting on Product key at the end of the loop through the YTD sheet.

Next month there will be YTD April and YTD May sheets. Maybe the user could be prompted to select the relevant sheet. Is that good practice ?

Toco
0
Martin LissOlder than dirtCommented:
The sorting on product key can be added to the macro.

If a YTD sheet is not the currently active sheet, the macro will already prompt the user the select a YTD sheet. If he then selects a YTD sheet that's already been used, there won't be any updates. There are a few different ways this could be fixed.
1. If there will never be YDT sheets from more than one year, then it would not be difficult to determine the proper sheet by looking at the month in the name.
2. A userform could created that lists the YTD sheets and asks the user to select one of them.
3. The user could me asked to enter the name of a sheet
4. After running the macro against the current YTD sheet, a cell like ZZ1 in that sheet could be updated with a value like "used" that would indicate that the sheet has been used previously by the macro. Then in future months the macro could examine the value of ZZ1in all YTD sheets and use the first one that it finds that's not "used". When done it would mark ZZ1 "used"
5. The macro could always run through all YTD sheets. This would take a little extra time to run, but unless the YTD sheets had 10's of thousands of rows it would not be very much time.

Your choice or let me know if you have a different idea.
0
TocogroupAuthor Commented:
I like the idea of having the user select the YTD mmm sheet to be processed. The workbook will only contain one year's worth of updates (eg 2015), so at the end of that year there will be 12 YTD month sheets. For the record, last year's YTD December sheet had a total of 8,000 rows (unique products).

There wouldn't be any necessity to run through ALL YTD sheets as the latest YTD sheet holds all the product totals up to and including that month for the year.

Yes, I agree, the ZZ1 option is a good belt and braces method to prevent the user running a YTD sheet previously run.
0
Roy CoxGroup Finance ManagerCommented:
here's a very simple trick that I was shown at college that requires no VBA at all., it works simply with formulas. All the YTD sheets must be the same layout and be placed between two sheets, I have called them Top and Bottom. This formula in the master sheet will sum all values in C2 in the sheets placed between these sheets.

=SUM(Top:Bottom!C2)

See this for a more detailed explanation

Summarise Data

Note moving sheets out of this range will allow you to see say 3 month's values, i'e you can add or remove sheets to see different results.
Product-YTD-Example-workbook.xlsx
0
Martin LissOlder than dirtCommented:
I like the idea of having the user select the YTD mmm sheet to be processed.
That sounds like you like #3 but
The workbook will only contain one year's worth of updates (eg 2015), so at the end of that year there will be 12 YTD month sheets
tells me that #1 is that way to go.
Yes, I agree, the ZZ1 option is a good belt and braces method to prevent the user running a YTD sheet previously run
With or without the indicator It doesn't matter if a sheet is used more than once. A given product will only be added to the Master if it doesn't already exist there.

Please let me know if you want to do #1 or #3 or some other one from my list.
0
Roy CoxGroup Finance ManagerCommented:
I forgot to mention in my example that you can add products to the master list and the formula copies down so simply when products are added to a certain month, simply add it to the master sheet and it will summarise .
0
TocogroupAuthor Commented:
Hi Roy,

Thanks for the explanation, however, the YTD Actual values for a product don't need to be totalled in my application. The YTD value provided for a product on the YTD April sheet will be the total amount sold from Jan to April. The figure provided on the YTD May sheet will be the total amount sold from Jan to May. So I don't have to actually total the amounts across the months, I just replace the latest YTD Actual figure on the Master with the latest YTD mmm amount.

Hope that clarifies the requirement.
0
TocogroupAuthor Commented:
Hi Martin,

I think giving the user the option which month to run is the way to go (#3). For example, the user may want to run the YTD April sheet updates on either April 30th or May 1st. Comparing the sheet name with today's month may not be what the user wants.
0
TocogroupAuthor Commented:
By the way Roy, I like that clever trick. That could be useful for another application I have.
0
Martin LissOlder than dirtCommented:
In #1 I wasn't proposing that the name be compared to the current date although that is a logical conclusion. Rather what I would do is to convert the month name in each tab to the numeric value of the month and then compare them against each other and use the one with the highest value.
0
TocogroupAuthor Commented:
Martin,
Oh I see. That's clever. Let's go with #1 then.
0
Martin LissOlder than dirtCommented:
OK, got to go out. Be back in a couple of hours.
0
Martin LissOlder than dirtCommented:
OK try this. I added 'YTD March' and 'YTD May' sheets and it correctly used the latter. BTW in order to determine the latest month the code only looks at the first 3 characters of the month, so if you had 'YTD Apr' and 'YTD Dec', etc it would still work.

Sub UpdateMaster()

Dim lngLastRow As Long
Dim lngNewRow As Long
Dim lngRow As Long
Dim rngFound As Range
Dim intCount As Integer
Dim strName As String
Dim intLatest As Integer
Dim sht As Worksheet

Application.ScreenUpdating = False

' Find the latest YTD sheet
For Each sht In Worksheets
    ' Only look at the YTD sheets
    If UCase(Left$(sht.Name, 3)) = "YTD" Then
        ' Get the month number from the first 3 characters of the month name
        ' in the sheet name and compare it to the highest previous value
        If Month(DateValue("01-" & Mid$(sht.Name, 5, 3) & "-1900")) > intLatest Then
            ' So far it's the higest so save the sheet name
            intLatest = Month(DateValue("01-" & Mid$(sht.Name, 5, 3) & "-1900"))
            strName = sht.Name
        End If
    End If
Next

' get the last row in the Master
lngLastRow = Range("A1048576").End(xlUp).Row
Sheets(strName).Activate

' See if the Product ID from the active YTD sheet is in the Master
With Sheets("Master").UsedRange
    For lngRow = 2 To lngLastRow
        Set rngFound = .Find(What:=ActiveSheet.Cells(lngRow, 1), LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
        If rngFound Is Nothing Then
            ' It's not so add it and the other fields to the Master
            With Sheets("Master")
                lngNewRow = .Range("A1048576").End(xlUp).Row + 1
                .Cells(lngNewRow, 1) = ActiveSheet.Cells(lngRow, 1)
                .Cells(lngNewRow, 2) = ActiveSheet.Cells(lngRow, 2)
                .Cells(lngNewRow, 4) = ActiveSheet.Cells(lngRow, 3)
                intCount = intCount + 1
            End With
        End If
    Next
End With

' Sort the Master
With Sheets("Master")
    .Activate
    .Columns("A:A").Select
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Worksheets("Master").Sort
        .SetRange Worksheets("Master").UsedRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ' Get rid of the column 'A' highlighting caused by the sort
    Cells(1000, 1).Select
    ActiveWindow.ScrollRow = 1
End With

MsgBox intCount & " products added to Master from sheet " & strName
Application.ScreenUpdating = True
End Sub

Open in new window

0

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
TocogroupAuthor Commented:
Oh yes. That works great.
I'm going to develop my application a little so I'll accept your solution and award you your points.
However, I may come back with a further question.

Many thanks for your help.
0
Martin LissOlder than dirtCommented:
OK.

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
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.