VBA to copy row in a workbook and auto fill in Master workbook.

**adriel19pr** · 03-28-2012, 03:24 PM

Question: I'm so lost, I can truly use the help. I have a workbook called “Datasheet1.xls" that I use to fill in with information for each individual cashier. I would like to have a VBA so that when I click on the button the information in the "Datasheet1.xls" range B209:O209 will start filling a different workbook called "MASTER1.xls" range "B4:O4". Once I reset the datasheet workbook. I can start filling it with the next cashier’s info, press the macro button then it will fill the next available line "B5:O5" in the “MASTER1.xls” workbook and so on to fill the Master workbook on a daily basis.

Don’t really know anything about VBA was just told that it was the only way to make it happen.

I will greatly appreciate all the help given.

Thanks

**rvasquez** · 03-28-2012, 05:00 PM

Hello adriel19pr,

Attached are your original spreadsheet with the modifications you requested.

Workbook 'Datasheet1' now has a command button that copies the range you specified and then opens the 'Master' workbook. You will need to right click on the command button and select view code, then you will need to Change the FILEPATH located in between the apostrophes to the filepath/location that you will be saving the 'Master' worksheet the I have attached.

When the Master worksheet opens it is set to run a macro that finds the first empty cell in Column B and adds the copied data from 'Datasheet1' to that row. Due to the fact that a macro runs when the worksheet is open I suggest saving both worksheets before trying to run the code and also changing the file path name in the 'Datasheet1' code first.

Please let me know if you have any questions.

Thanks

RVASQUEZ

**adriel19pr** · 03-29-2012, 02:12 PM

Thank You very much it worked like a charm. I greatly appreciate all you've done for me. Did I say Thank You? Where can I rate you at as in star ratings?
I would like to know if I may be able not only copy over the totals, as you already helped me. I would also like to be able and copy over cashier name to the totals. Which is in "H1" in the datasheet to be copied over to "A4" of the Master1. If you can help I will also appreciate it. If don't have the time I truly understand. Thank You, Adriel Morales

**rvasquez** · 03-29-2012, 03:11 PM

Adriel19pr,

Aww thanks I appreciate it, I'm not sure how the whole rating thing works I'm just a little bit of a nerd and actually enjoy doing this for fun.

That being said, I have revised the code to fit your request. I made just a small adjustment by adding a column in between the total Column and the totals and then hid that column (column B). So now Cell B209 is equal to the value of I1(the Cashier name).

Since the code actually selected and copied cells B209 through O209 before I didn't need to make an adjustment to the code located in the Datasheet1 spreadsheet.

Now in the Master1 spreadsheet I had to adjust some code as well as add some. I'm glad you had this request because I wouldn't of thought of a situation when you needed to just open the Master1 spreadsheet. The code I gave you before would've given you an error everytime if you tried to open it alone without the Datasheet1 workbook.

So now there is an errorhandler in the Master1 workbook_open event at the top that exits the code if you get an error.

Also, I change the 3rd line of the code on the workbook_open event to an A between the apostrophes to incorporate the Cashier column.

If you use the worksheets I provided, don't forget to follow the instructions from my previous post to change the filepath name.

Please let me know if you have any more questions and thanks again!

RVASQUEZ