Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Automatically update master worksheet from other worksheets [Solved/Closed]

Report
Posts
6
Registration date
Saturday November 18, 2017
Status
Member
Last seen
November 21, 2017
 -
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 -
Okay, first I should mention that I'm a complete amateur when it comes to excel. No VBA or macro experience, so if you're not sure whether I know something yet, I probably don't.

I have a workbook with 6 worksheets inside; One of the sheets is a master; it's simply the other 6 sheets compiled into 1 big one. I need to set it up so that any new data entered into the new separate sheets is automatically entered into the master sheet, in the first blank row.

The columns are not the same across all the sheets. Hopefully, this will be easier for the pros here than it's been for me, I've been banging my head against the wall on this one. I'll be checking this thread religiously, so if you need any more information just let me know...

Thanks in advance for any help.
Related:

8 replies

Answer 1 / 8
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello Saktivel,

There are a number of ways that this can be done but could you please upload a sample of your workbook to a free file sharing site such as ge.tt or Drop Box and then post the link to your file back here.
Seeing your file will give us a better understanding of what you intend to do and will allow us to test any suitable code. Please use dummy data in your sample.

Thank you Saktivel.

Cheerio,
vcoolio.
2
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report
Posts
6
Registration date
Saturday November 18, 2017
Status
Member
Last seen
November 21, 2017
Dear voolio,

Good day to you,

Thank you for the respond and here is the link :
https://www.dropbox.com/s/h5wh1gc9n4p4bs3/sample.xlsx?dl=0

Thank you,
Saktivel
Answer 2 / 8
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello Saktivel,

So you are just creating an invoice register for your individual clients. Is this correct?

Cheerio,
vcoolio.
Report

Hi vcoolio,

Good day to you,
Yes I'm creating an individual invoice so that every single update should go to master data.

Thank you,
Saktivel
Answer 3 / 8
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello Saktivel,

In your sample, I don't quite follow the logic of the Master sheet.

Could you please explain where the values for Column R (Main Course) are sourced from the invoices.
Could you also explain where the items are sourced from (Item 1, Item 2, Item 3 etc.). Are these supposed to be sourced from Column A of the invoices where you only have "No" as the column heading?
Are there supposed to be ten item lines? If so, the G.S.T. will interfere with these lines. GST needs to be part of the Totals section, immediately below all the item lines.

For this to work seamlessly, each invoice for each client needs to be of a fixed size. I would recommend a standard invoice template for your purposes.

Awaiting your reply.

Cheerio,
vcoolio.
Report

Dear vcoolio,

Good day to you,

Sorry for the late reply and for the values for Column R (Main Course)= B24 should be fixed as the title of the event.

among the items are sourced from (Item 1, Item 2, Item 3 etc.) column B26 should be fixed.

Are these supposed to be sourced from Column A of the invoices where you only have "No" as the column heading? = Column I12

GST needs to be part of the Totals section, immediately below all the item lines. (Yes)

All the invoices should be of fixed standard size.

Thank you,
Saktivel
Answer 4 / 8
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello Saktivel,

I need some further clarification:-

1) The Event Title will always be in cell B24. Is this correct?
2) You will only ever use cell B26 for the item description. Is this correct?
3) Does this mean that the item number will always be in cell A26?
4) Your invoice format does not have any values in cell I12 and does not appear to be a part of the invoices. Please advise.

Are you open to some suggestions to make the whole process more fluent? I see a number of problems with the current format of the invoices which could unnecessarily complicate the fluent use of a VBA code.

Let me know your thoughts.

Cheerio,
vcoolio.
Report
Posts
6
Registration date
Saturday November 18, 2017
Status
Member
Last seen
November 21, 2017
Hello vcoolio,

Good day to you,

1) The Event Title will always be in cell B24. Is this correct? Yes
2) You will only ever use cell B26 for the item description. Is this correct? we make it standardize into B25.
3) Does this mean that the item number will always be in cell A26? A25
4) Your invoice format does not have any values in cell I12 and does not appear to be a part of the invoices.Yes, there is no value.

I'm sending the standard format to be used:
https://www.dropbox.com/s/bzv1nsffnae09cv/sample%201.xlsx?dl=0
Please advise me if this format should be ok to use in future.

Thank you,
Regards,
saktivel
Answer 5 / 8
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello Saktivel,

As you appear to have a separate invoice sheet for each client, I have created a code that will need to be run from each individual sheet. Following is the link to your sample file:-

http://ge.tt/9wM0aOn2

You will notice that I have placed a "RUN" button on each sheet. Click on the button once all the required information has been entered. The data will then be transferred to the Master sheet into the next available row.

You may also notice the minor changes that I have made to the invoices. This is just a little tidy up which helps to make the transfer of data as seamless as possible. On two of the invoices, you will see that I have added an additional line in the description field with some values extended. This is just to show you that any additional data added to the invoices will be transferred to the correct column in the Master sheet. Following are the codes associated with each invoice:- 


Sub MoveIt1()

    Dim CopyArr As Variant, PasteArr As Variant, X As Long
    Dim ws As Worksheet, ws1 As Worksheet
    Set ws = Sheet1
    Set ws1 = Sheet7
    
nextrow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

Application.ScreenUpdating = False

CopyArr = Array("H12", "H13", "A12", "A13", "A14", "A15", "A16", "A17", "A20", "H17", "H14", "H15", "H16", "E23", "B24", _
"B25", "B26", "B27", "B28", "B29", "B30", "B31", "B32", "B33", "B34", "H25", "H26", "H27", "H28", "H29", "H30", "H31", "H32", "H33", "H34")
PastArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "L", "M", "N", "O", "P", "Q", "R", "S", "U", "W", "Y", "AA", "AC", "AE", _
"AG", "AI", "AK", "T", "V", "X", "Z", "AB", "AD", "AF", "AH", "AJ", "AL")

For X = LBound(CopyArr) To UBound(CopyArr)
        ws1.Range(CopyArr(X)).Copy
        ws.Range(PastArr(X) & nextrow).PasteSpecial xlPasteValues
        Next
ws.Columns.AutoFit

Application.CutCopyMode = False
Application.ScreenUpdating = True
ws.Select

End Sub

Sub MoveIt2()

    Dim CopyArr As Variant, PasteArr As Variant, X As Long
    Dim ws As Worksheet, ws1 As Worksheet
    Set ws = Sheet1
    Set ws1 = Sheet8
    
nextrow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

Application.ScreenUpdating = False

CopyArr = Array("H12", "H13", "A12", "A13", "A14", "A15", "A16", "A17", "A20", "H17", "H14", "H15", "H16", "E23", "B24", _
"B25", "B26", "B27", "B28", "B29", "B30", "B31", "B32", "B33", "B34", "H25", "H26", "H27", "H28", "H29", "H30", "H31", "H32", "H33", "H34")
PastArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "L", "M", "N", "O", "P", "Q", "R", "S", "U", "W", "Y", "AA", "AC", "AE", _
"AG", "AI", "AK", "T", "V", "X", "Z", "AB", "AD", "AF", "AH", "AJ", "AL")

For X = LBound(CopyArr) To UBound(CopyArr)
        ws1.Range(CopyArr(X)).Copy
        ws.Range(PastArr(X) & nextrow).PasteSpecial xlPasteValues
        Next
ws.Columns.AutoFit

Application.CutCopyMode = False
Application.ScreenUpdating = True
ws.Select

End Sub


Sub MoveIt3()

    Dim CopyArr As Variant, PasteArr As Variant, X As Long
    Dim ws As Worksheet, ws1 As Worksheet
    Set ws = Sheet1
    Set ws1 = Sheet2
    
nextrow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

Application.ScreenUpdating = False

CopyArr = Array("H12", "H13", "A12", "A13", "A14", "A15", "A16", "A17", "A20", "H17", "H14", "H15", "H16", "E23", "B24", _
"B25", "B26", "B27", "B28", "B29", "B30", "B31", "B32", "B33", "B34", "H25", "H26", "H27", "H28", "H29", "H30", "H31", "H32", "H33", "H34")
PastArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "L", "M", "N", "O", "P", "Q", "R", "S", "U", "W", "Y", "AA", "AC", "AE", _
"AG", "AI", "AK", "T", "V", "X", "Z", "AB", "AD", "AF", "AH", "AJ", "AL")

For X = LBound(CopyArr) To UBound(CopyArr)
        ws1.Range(CopyArr(X)).Copy
        ws.Range(PastArr(X) & nextrow).PasteSpecial xlPasteValues
        Next
ws.Columns.AutoFit

Application.CutCopyMode = False
Application.ScreenUpdating = True
ws.Select

End Sub


The codes are basically the same, the only difference being the sheet references ws and ws1 so be wary of this as you add more invoices to your workbook. The reference ws is always Sheet1(Master) in each code. Each code needs to be assigned to its own button.

For this to all work correctly, each invoice needs to be formatted exactly the same as those that you see in the attached sample. It may be wise to create a template invoice and use this each time instead of trying to create a new one for each client.

Test the codes in a copy of your actual workbook first.

I hope that this helps.

Cheerio,
vcoolio.
Report
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Sorry Saktivel,

This is the correct link:-

http://ge.tt/4p91gOn2

The other was just another test file.

Cheerio,
vcoolio.
Report
Posts
6
Registration date
Saturday November 18, 2017
Status
Member
Last seen
November 21, 2017
Dear vcoolio,

Thank you for the coding and a small request can we add the code for duplicate record found.

Thank you.
Saktivel
Answer 6 / 8
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello Saktivel,

I'm not sure what you mean. Where would you have duplicates?

Could you please elaborate.

Thank you.

vcoolio.
Report
Posts
6
Registration date
Saturday November 18, 2017
Status
Member
Last seen
November 21, 2017
Hello vcoolio,

Good day to you,

the duplicate should be detected in the master sheet when they entered in the invoice.
eg. invoice number repetation.

Regards,
Saktivel
Answer 7 / 8
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello Saktivel,

So you wish to remove any duplicate entries (entire rows) in the Master sheet based on the invoice number. Is this correct?

- Why would you have duplicate invoice numbers? Invoice numbers should be unique to each invoice.
- From where and how are your invoice numbers generated?

Cheerio,
vcoolio.
Report

Hello violin,

Good day to you,

Yes indeed, I need to remove the duplicate entry.
Because the invoice number are manually key in based on customer uniq Id.
Answer 8 / 8
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
Hello Saktivel,

Following is a code that will remove duplicates in the Master sheet for you:- 

Sub RemoveDupes()

Application.ScreenUpdating = False

Range("AU2:AU" & Cells(Rows.Count, 1).End(xlUp).Row) = "=COUNTIF($A$2:$A2,A2)>1"
Range("AU:AU").AutoFilter 1, "True"
Range("A2:A" & Cells(Rows.Count, 2).End(xlUp).Row).EntireRow.Delete
Range("AU:AU").AutoFilter
Range("AU:AU").ClearContents

Application.ScreenUpdating = True

End Sub


If you are wondering, I have just used Column AU as a helper column only.

Place this code at the bottom of the other codes in the same module and then assign it to a button on the Master sheet.

When you have a number of entries on the Master sheet, just click on the button to remove any duplicates.

Following is the link to the updated sample file:-

http://ge.tt/5nKShQn2

In the sample file, transfer data (by clicking on the buttons on each individual sheet) a number of times just to duplicate some entries. Go back to the Master sheet and click on the "Remove Duplicates" button. You will notice that duplicates are removed.

I hope that this helps.

Cheerio,
vcoolio.
Report
Posts
6
Registration date
Saturday November 18, 2017
Status
Member
Last seen
November 21, 2017
Dear vcoolio,

Good day to you,

Thank you very much for your help. it's really helpful.

Thank you,
Regards,
Saktivel
Report
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220 >
Posts
6
Registration date
Saturday November 18, 2017
Status
Member
Last seen
November 21, 2017
You're welcome Saktivel. I'm glad that I was able to help.

Cheerio,
vcoolio.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!