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...
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.
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.
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.
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.
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:-
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.
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?
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.
5638 users have said thank you to us this month
- Posts
- 6
- Registration date
- Saturday November 18, 2017
- Status
- Member
- Last seen
- November 21, 2017
-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