Automatically update master worksheet from other worksheetsSolved/Closed

Question

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.

vcoolio · Accepted Answer

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.

vcoolio · Answer

Hello Saktivel,

So you are just creating an invoice register for your individual clients. Is this correct?

Cheerio,
vcoolio.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

Hello Saktivel,

I'm not sure what you mean. Where would you have duplicates?

Could you please elaborate.

Thank you.

vcoolio.

vcoolio · Answer

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.

vcoolio · Answer

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.

Automatically update master worksheet from other worksheets

8 responses

Similar discussions

Subscribe To Our Newsletter!