Solved Auto Insert New Row on Different Worksheet

January 14, 2016 at 04:57:41
Specs: Windows 7
I have a spreadsheet with 10 worksheets on it, 1 for each supplier with the same standard layout on each:
Column A: Product Code
Column B: Product Name
Column C: Cost Price
Column D: Sales Prices

I use these individual spreadsheets to import my products to sage. It would make life a lot easier if I had 1 worksheet in this same file with all the information from the others so that I would only have to import 1 file e.g. every time i add a row of information to either one of those 10 supplier sheets it adds a new row with the same information into the master sheet. How would i do this?


See More: Auto Insert New Row on Different Worksheet

Report •


✔ Best Answer
January 17, 2016 at 17:30:44
Let's get the code into your workbook, then deal with the options for running it.

First, I am assuming that you are using Excel 2007 or later on a Windows based machine. If you are using an earlier version or using a Mac, these steps may not work exactly as written. Let me know.

Second, I suggest that you do this in a backup copy of your workbook. Macros cannot be undone and I'd hate to have you run the code and then save your original book before you know if the code does what you want it to do.

1 - Copy the code from Response #4 to store it on the clipboard. Your copy should start with Sub BuildImportSheet() and end with End Sub, inclusive
2 - In your workbook, press Alt-F11 to open the VBA editor
3 - Click the Insert tab and choose Module
4 - Paste the code into the pane that opens. You should not see any red text. If you do, you copied more (or less) than the actual code.
5 - Close the VBA editor
6 - Save the workbook. Since it now contains a macro, it must be saved as an xlsm or xlsb file. xlsm will be fine.

OK, now that the macro is in the file, you have a number of options for running it. I'll offer 3 options, although there are many more. A Google search on Macro buttons will turn up a lot of info. In my opinion, Option 2 is probably the best of the 3.

Option 1:

The most brute force, basic method is to open the VBA editor via Alt-F11, open the module with the code, place the cursor anywhere inside the code and click the green Run triangle in the top ribbon. You probably won't even realize that anything happened, but the last sheet in the workbook should contain all the data from the other sheets.

In most cases, you (or your users) are not going to want to open the VBA editor every time you want to run the code, so a better idea is to create a button in the workbook so the code can be run with a click. Options 2 and 3 are different ways to create a macro button.

Option 2:

Place a button on your Quick Access Toolbar. (QAT)

1 - Right click the QAT and choose Customize Quick Access Toolbar
2 - Click the drop down for "Choose Commands From" and choose Macros
3 - BuildImportSheet should appear in the pane below the drop down
4 - Select BuildImportSheet, click Add to add it to the right hand pane
5 - Click the drop down for Customize Quick Access toolbar and choose the name of your workbook. (This done so that the button does not show up in every workbook you open)
6 - If you want, you can click the Modify button below the right hand pane and choose a different shape for the button. I have about 20 macro buttons that I use all the time, so I try to choose a shape that reminds me of what each macro does.
7 - Click OK to close the Customize dialog box.
8 - Save the workbook

In the QAT, you should see the new button. Hover over it and the macro name should appear. Click it and the code should run. Whenever you open the workbook, the button should be there.

(In case you didn't know it, you can add all sorts of buttons to the QAT to make Excel easier to use. Not just macro buttons, but buttons for just about every Excel feature that can be found hidden behind the various ribbons.)

Option 3:

1 - On any worksheet, click the Insert tab and choose any shape you'd like
2 - Place the shape on the worksheet. You can add text to the shape if you want to
3 - Right click the shape and choose "Assign Macro"
4 - In the dialog box that opens, you should see the BuildImportSheet macro
5 - Double click the macro name and the dialog box should close
6 - When you hover over the shape, the cursor should become a finger, telling you that it is a "link". If you click the button, the macro will run

You can add a shape button on every sheet if you want to. However, if you are going to add a shape button to every sheet, you might as well just add a single button to the QAT.

Let me know if this makes sense and if you need any further help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
January 14, 2016 at 09:50:11
Without knowing the process you use "every time you add a row of information to either one of those 10 supplier sheets" it's hard to offer you complete solution at this time.

A macro could be written to copy each new row automatically, such as when you enter a value in e.g. Column D. The danger with that method is that it could lead to duplicates if you make a change to column D after the row has already been copied. In other words, if the macro is triggered by a "change" to a cell in Column D, it will not (easily) be able to tell if the change was a new entry or a change to existing piece of data.

It might be safer to use a macro to essentially recreate the summary sheet just prior to exporting it. A fairly simple macro could clear the existing data in the summary sheet and then copy all the data from each individual sheet with the click of a button. With that method, you would always have the most recent data as of when the button was clicked. Another option would be to automatically create an updated copy of the summary sheet each time the workbook is saved and/or closed.

As I said, without knowing your process, we need to make sure that we offer something that is as robust and as bullet proof as possible. Based on my comments above, if you could supply some information as to how you use the workbook that might help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
January 16, 2016 at 06:15:56
OK I dont know an awful lot about excel but I'll do my best.

The layout is as follows, standard for each of the 15 Worksheets.

Column A: Is product code which is just an auto format number made by the initials of the supplier and 001 or 002 afterwards.

Column B: Is product name with no formatting.

Column C: Is Tax code, a field required for Sage, in our case it is code T1, again no formatting except the autocopy feature.

Column D: Is Sales Price, which is formatted to be currency (£). There is a formula which is replicated the whole way down each worksheet. It is =(E2*1.3) and then this is dragged down to get E3*1.3 etc. The 1.3 various between worksheets sometimes being 1.5 or 1.35 e.g.

Column E: Is cost price and has a formula again replicated in the same manner of =(F2*0.8) where F2 represents the euro cost (Column F) and 0.8 in the euro to £ conversion rate.

Column F: Euro Cost, only currency formatting.

Column G: Unit of Sale, usually 'Each'

Column H: Category which is entered as a number, each representing a category number in the sage software.

Column I: Supplier Ref, allows us to see who supplies it

Column J: Is 'Weight' and this is almost always left blank as well as column K 'Country of Origin.

Column L: Supplier Code, which is just the account code for that particular supplier on our Sage software.


I wont name each supplier but I will give the initials to allow you to suggest the code:

WS1: AT
WS2: BT
WS3: ES
WS4: JT
WS5: JE
WS6: KB
WS7: MD
WS8: ML
WS9: NW
WS10: RT
WS11: S
WS12: SP
WS13: TR
WS14: WI
WS15: Tab to Import

So I have all the products from all these worksheets imported already, its just that we get new products in all the time so it would very handy if we were able to just add them to the bottom of the supplier worksheet and it automatically adds it to a new row in the import sheet. We manually enter the data onto excel to import automatically on sage. It would also be useful that if we were to change a price on it that it only changes the price on the existing product in the import page without adding a new one on the importing page.

Sorry for the length of the post,I just wanted to be clear. I look forward to the reply. Thanks


Report •

#3
January 16, 2016 at 07:14:11
Thank you for the detailed explanation. No need for the apology, it is better to supply more info than is required than less.

Based on this statement...

"It would also be useful that if we were to change a price on it that it only changes the price on the existing product in the import page without adding a new one on the importing page."

...it sounds like recreating the import sheet just prior to the "import" makes the most sense. Here is why I say this:

VBA, the language in which macros are written, has a feature known as a "Worksheet_Change" macro. It is essentially a macro that runs automatically whenever a worksheet is changed, based on whatever criteria you choose, e.g. any change at all, a change only within a certain column or row or range, the entry of a specific value, etc. This would be fine if all you are doing is adding new data and wanting that data copied to the bottom of the import page. However, since you want to be able to change data in existing rows and then have the change reflected on the import sheet, things get a little more complicated.

VBA would have to "decide" if the change made to the worksheet was the entry of new data (therefore add a new row on the import page) or a change to existing data (therefore first find that data on the import page and then make the change just to the "price", for example. That "decision process" is complicated to construct.

However, if we simply clear the import page and recreate it whenever you want to, the process is really not much more than an an automated delete/copy/paste operation. In essence the code would:

1 - Delete all data in WS15, other than the header row
2 - Loop though WS1:WS14, copy/paste all data, except for the header row, into WS15

If that process is acceptable, the next question is this:

When do you want the macro to run? It can be set up to run manually with the click of a button, automatically when the user saves the workbook, automatically when a specific sheet is selected, etc. That is up to you. The main thing to consider is how long the creation of the import sheet will take. If it will be relatively slow process, we wouldn't want the code to run every single time a change was made. The time it takes for the code to run will depend on a number of factors, such as the amount of data to copy, the speed of the machine it is running on, any network issues that might come into play, etc.

What are your thoughts on when the import sheet should be "recreated"? Don't worry about not knowing about Excel or macros, just give me your thoughts on when, as part of your process, you think it makes sense to create the import sheet and we'll see if we can make that happen.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 16, 2016 at 08:55:32
Just to get it into the thread, this is the macro that could be used to recreate the Import Sheet whenever you want.

The code assumes that the last worksheet in the workbook is the sheet used for the import.

As I mentioned earlier, the code will clear all the data from the last worksheet and then copy/paste the data from each individual worksheet, in sequential order.

Sub BuildImportSheet()
Dim shtNum As Long, lastRow As Long, nxtRow As Long

Application.ScreenUpdating = False

'Clear the last sheet in the workbook and
'copy column headers from Sheet1
   Sheets(Sheets.Count).Cells.ClearContents
   Sheets(1).Rows(1).Copy Sheets(Sheets.Count).Cells(1, 1)
   
'Loop through sheets, determine last Row to copy, paste into
'next available Row in the last sheet of the workbook
    For shtNum = 1 To Sheets.Count - 1
      lastRow = Sheets(shtNum).Cells(Rows.Count, 1).End(xlUp).Row
       nxtRow = Sheets(Sheets.Count).Cells(Rows.Count, 1).End(xlUp).Row + 1
         Sheets(shtNum).Rows("2:" & lastRow).EntireRow.Copy _
             Sheets(Sheets.Count).Cells(nxtRow, 1)
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#5
January 16, 2016 at 17:51:26
We have about 4500 entries on the import page to date and I'm assuming this is quite a lot of data? If this is the case we could probably set a day every week to update and import or something along those lines? Don't worry about the change function if it makes things too complicated, we could always change it on the sage software itself

Report •

#6
January 16, 2016 at 19:58:32
4500 rows is not a lot of data. That's an average of about 322 rows per sheet, right?

I put 4500 rows of data (no formulas) in all 14 sheets and the code I offered in Response # 4 took less than 1 second to run.

That's 63,000 rows in the Import Sheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
January 17, 2016 at 11:43:12
How do I apply this macro thing then? Like where do I insert this code? Thanks

Report •

#8
January 17, 2016 at 17:30:44
✔ Best Answer
Let's get the code into your workbook, then deal with the options for running it.

First, I am assuming that you are using Excel 2007 or later on a Windows based machine. If you are using an earlier version or using a Mac, these steps may not work exactly as written. Let me know.

Second, I suggest that you do this in a backup copy of your workbook. Macros cannot be undone and I'd hate to have you run the code and then save your original book before you know if the code does what you want it to do.

1 - Copy the code from Response #4 to store it on the clipboard. Your copy should start with Sub BuildImportSheet() and end with End Sub, inclusive
2 - In your workbook, press Alt-F11 to open the VBA editor
3 - Click the Insert tab and choose Module
4 - Paste the code into the pane that opens. You should not see any red text. If you do, you copied more (or less) than the actual code.
5 - Close the VBA editor
6 - Save the workbook. Since it now contains a macro, it must be saved as an xlsm or xlsb file. xlsm will be fine.

OK, now that the macro is in the file, you have a number of options for running it. I'll offer 3 options, although there are many more. A Google search on Macro buttons will turn up a lot of info. In my opinion, Option 2 is probably the best of the 3.

Option 1:

The most brute force, basic method is to open the VBA editor via Alt-F11, open the module with the code, place the cursor anywhere inside the code and click the green Run triangle in the top ribbon. You probably won't even realize that anything happened, but the last sheet in the workbook should contain all the data from the other sheets.

In most cases, you (or your users) are not going to want to open the VBA editor every time you want to run the code, so a better idea is to create a button in the workbook so the code can be run with a click. Options 2 and 3 are different ways to create a macro button.

Option 2:

Place a button on your Quick Access Toolbar. (QAT)

1 - Right click the QAT and choose Customize Quick Access Toolbar
2 - Click the drop down for "Choose Commands From" and choose Macros
3 - BuildImportSheet should appear in the pane below the drop down
4 - Select BuildImportSheet, click Add to add it to the right hand pane
5 - Click the drop down for Customize Quick Access toolbar and choose the name of your workbook. (This done so that the button does not show up in every workbook you open)
6 - If you want, you can click the Modify button below the right hand pane and choose a different shape for the button. I have about 20 macro buttons that I use all the time, so I try to choose a shape that reminds me of what each macro does.
7 - Click OK to close the Customize dialog box.
8 - Save the workbook

In the QAT, you should see the new button. Hover over it and the macro name should appear. Click it and the code should run. Whenever you open the workbook, the button should be there.

(In case you didn't know it, you can add all sorts of buttons to the QAT to make Excel easier to use. Not just macro buttons, but buttons for just about every Excel feature that can be found hidden behind the various ribbons.)

Option 3:

1 - On any worksheet, click the Insert tab and choose any shape you'd like
2 - Place the shape on the worksheet. You can add text to the shape if you want to
3 - Right click the shape and choose "Assign Macro"
4 - In the dialog box that opens, you should see the BuildImportSheet macro
5 - Double click the macro name and the dialog box should close
6 - When you hover over the shape, the cursor should become a finger, telling you that it is a "link". If you click the button, the macro will run

You can add a shape button on every sheet if you want to. However, if you are going to add a shape button to every sheet, you might as well just add a single button to the QAT.

Let me know if this makes sense and if you need any further help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
January 23, 2016 at 02:37:00
Works an absolute treat! Thanks for everything!

Report •

#10
January 23, 2016 at 16:48:58
I'm glad I could help.

If you are going to use macros in your workbooks, you should have at least some understanding of how they work.

When you have some free time, you should read this tutorial and practice using the debugging techniques on the code that I offered. By "troubleshooting" code that is working, you will not only get a better understanding of how the code works, but you will learn about the debugging techniques so you will be familiar with them when you really need them. I learned most of what I know about VBA by "reverse engineering" code that I found on the web.

http://www.computing.net/howtos/sho...

message edited by DerbyDad03


Report •

#11
February 13, 2016 at 03:00:40
Hi, me again. Your macro has worked a treat so far. The next stage of my project is a website and I am trying to have a worksheet in the same excel file for the website. Is there a macro which allows me to for example have a column in each workbook named 'Website' and for each row of data that i type 'YES' into to automatically copy into a worksheet named 'Website Products'. Thanks for your time and efforts!

Report •

#12
February 13, 2016 at 20:22:07
Since this is an unrelated question, please start a new thread with a relevant subject line.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question