PDA

View Full Version : Solved: Button to insert row in multiple worksheets and update



cc9083
04-25-2009, 06:35 AM
Hi there. I had posted this in another forum but there was no solution. Hope there's someone here that can help.

I have a workbook with 3 worksheets namely Work No. Register, User Register and Work Value Per User. The purposes of the worksheets are listed below.

Work No. Register - Main worksheet which indicates the works to be assigned to Users. These data are updated daily.
User Register - Details on works sorted by User.
Work Value Per User - Each and total value of work sorted by User
Both the User Register and Work Value Per User worksheet receives data from Work No. Register.

Now, I would like to create two buttons in Work No. Register.

Button 1 - inserts row
Button 2 - undo the inserted row

I would like the new row to be inserted directly under the header (as per After.xls) and should include all the formulas from the previous row. However, I have some concerns.

If unique data (new work no. and/or new user) is entered in Work No. Register under the new row then a new row must also be added in both the User Register and Work Value Per User for further processes.

Can this be done?

Here are my before and after examples.

cc9083
04-25-2009, 06:37 AM
Here is my after file.

mdmackillop
04-25-2009, 07:30 AM
Try this. Noter that I have changed the Totals formulae to include the headers. It makes code simpler. Formulae are copied and pasted. I've not tested them.
I don't follow the last part of your question.

mdmackillop
04-25-2009, 07:31 AM
BTW, in your other posting you give a link to this site.

cc9083
04-25-2009, 09:28 PM
Hi Macmadkillop. Thanks for the reply. However I can't seem to download your file as it says macros cannot be found. Maybe you can look into it again.

I'll describe the situation again just to be clear. Let's start and say that all 3 worksheets are blank. The first data received every morning are entered into the Work No. Register worksheet. These data has unique Work No. (no duplicates) and User ID. Some User may have more than one Work Nos. at a given time to be precise. This worksheet is a main checklist to monitor the corresponding number of works as well as the User assigned to each Work No. These data are subsequently tabulated in both User Register and Work Value Per User worksheets. The said worksheets are used to monitor the number of works, the works detail and value of work for each User. So the 3 worksheets are in fact correlated.

I would like to have 2 buttons that creates; 1) a row in the Work No. Register worksheet as well as row in the subsequent worksheets (if required) and 2) undo the row of the created row (sort like a back button).

The first button is positioned in the Work No. Register and shall create a row with the formulas intact just below the header. Any data entered in this row shall be automatically captured in the following worksheets by means of additional row equipped with the required formulas. However, since the User ID entered in the first worksheet can be in duplicates as opposed to the unique Work No., the following 2 worksheets must be alerted so as not to perform a duplicate entry in their worksheets. In other words, the User Register and Work Value Per User worksheet each do not add an additional row when the new User ID entered in the first worksheet is already registered in their User list. The action only to be perform here is to update the number of works, the works detail and value of work.

I do hope that my description above is crystal clear and that such automation can be performed as it will save me valuable time. These are actually the additional scope of works to my already overloaded work since the downsizing of my company. Thanks in advance.

mdmackillop
04-26-2009, 02:26 AM
The code is there; check your Macro/anti-virus.

Having looked at this again, it need some more work from you.
Use the After workbook; The Before one has formula references to After which is confusing/wrong
Ensure that Row 4 down contain correct and working formulae (I'm not going to check them), but no Constant values. This will provide the formulae for a new row inserted below.
Row 4 will be hidden; the new row will be inserted at Row 5.



However, since the User ID entered in the first worksheet can be in duplicates as opposed to the unique Work No., the following 2 worksheets must be alerted so as not to perform a duplicate entry in their worksheets. In other words, the User Register and Work Value Per User worksheet each do not add an additional row when the new User ID entered in the first worksheet is already registered in their User list.

Your formulae should deal with duplicate entries. It will not deal with new users inserted against existing jobs. That is a management issue.
Your workbook should contain crosschecks that the count/total of items is correct across the sheets.

Once you have a "proper" book, it can then be automated for new rows to be inserted.

cc9083
04-26-2009, 11:00 PM
Hi mdmackillop.

Thanks for the advice. Managed to download the file and correct the formulas. The buttons are working great except that Button 1 returns a request for an action to done, "Select destination and press ENTER or choose Paste" in Work Value Per User worksheet. I have to press the Esc button to escape whenever the request appears. Is the a way to remove this request?

As for the duplicate entries, i will find the correct formulas in this forum to detect duplicate entires and execute the necessary actions on the worksheets. I will also place some crosschecks as per your advice across the sheets.

Once I've achieved a proper workbook, it will be available for download in this forum. Thanks again.

mdmackillop
04-27-2009, 12:25 AM
If set up as suggested, there will be no copy/paste in the final version. Untested but try

Option Explicit
Sub Macro1()
Dim WRN As Worksheet
Dim UR As Worksheet
Dim WVPU As Worksheet

Set WRN = Sheets(1)
Set UR = Sheets(2)
Set WVPU = Sheets(3)

WRN.Range("A5:C5").Insert Shift:=xlDown
UR.Range("A5:J5").Insert Shift:=xlDown
UR.Range("A5:J5 ").FillDown

WVPU.Range("A5:S5").Insert Shift:=xlDown
WVPU.Range("A5:S5").FillDown
End Sub
Sub Macro2()
Dim WRN As Worksheet
Dim UR As Worksheet
Dim WVPU As Worksheet

Set WRN = Sheets(1)
Set UR = Sheets(2)
Set WVPU = Sheets(3)

WRN.Range("A5:C5").Delete Shift:=xlUp
UR.Range("A5:J5").Delete Shift:=xlUp
WVPU.Range("A5:S5").Delete Shift:=xlUp
End Sub

cc9083
04-27-2009, 06:15 AM
Hi mdmac.
It works like a charm. However may need to drop this as my array formula in User Register worksheet has gone haywire. Apparently, the inserted row at the top of the table only works for standard formula (works great in WorkValue Per User worksheet) and not for array formula. My array formula is unable to include the inserted row as part of its range. Maybe you can advise me on the appropriate formula to use. Hope there's a solution here.

mdmackillop
04-27-2009, 10:28 AM
Use Dynamic Range names for your data. It greatly simplifies the formulae and the insertion of rows. You could also consider a Pivot Table as on Pivot Sheet

cc9083
05-04-2009, 04:42 AM
Hi mdmac. Thanks for the advice. I had look at the dynamic range names and tweaked some formulas to suit my requirement. However, I still need some of your further advice on the duplicate entries. Anyway, I consider my problem here as solved and would move to a separate post for duplicate entries as per following link.

http://www.vbaexpress.com/forum/showthread.php?p=184167#post184167

Thanks again.