Copy & Paste From Different Workbooks to MasterSheet

Question

I have one workbook with 8 sheets per country (i.e. France, Belgium, etc.). Apart from these sheets I have "Mastertab" and "Database".

Country-sheets are created in the same way (they have the same template). These templates are made to track expenses (therefore for example, France may have 10 expenses, Belgium 3 and UK 81).

What I would like to do is to create a macro (because I think it is impossible with formula if I want to avoid blank rows) that will:

1. Find last row where is any data/value in column K.
2. Select all data (row that will be indicated in point 1 and columns B-P).
3. Paste this selection to the Mastertab.
4. Continue doing 1-3 for other countries/sheets KNOWING that data should be pasted just below the previous one (so it has to find first blank row and paste data there).

Example: First row with data for each country is 18. This is the place where first line with expense is posted. User will fill in only data in column K (all other columns are filled with formulas, vlookups, etc.)

Assuming that I have 3 expenses for France, I want Excel to select B18:P20 and paste into Mastersheet in cell B18.*

*Next pasting in Mastersheet should be from cell B21.

Hope it is clear. :)

Thank you in advance, Marek

Answer 1

you would definitely be stretching the limitations of Excel formulas if you tried using formulas to do this.

You can use VBA to do this, but you're going to probably going to need the user to trigger the macro every time they make an entry. If you need the master to be dynamically updated then that's going to be harder again.

I think the cleanest way to do this is to have a macro that builds the master list from scratch every time you run the macro, i.e. deletes the Master sheet, and recreates it again. This way you don't have to deal with reorganizing the master output every time a new row is added. That would require a lot of additional logic.

If you are doing a multi-user environment with concurrent updates, then this approach will not work, as you would need an additional server to handle messaging between each user. At this point, you're better off not using Excel.

If each user will add their data, run the macro and send the sheet to the next user you could concoct a macro approach.

From a very high level:

• Create a macro to co-ordinate all that comes below
  • Delete everything in your master (MasterSheetName.Cells.ClearContents will keep the formats if you need it)
  • Create an index which keeps track of the last row with data in your master sheet. This will start at 1
• Iterate through each of the country sheets
  • https://support.microsoft.com/en-ie/help/142126/macro-to-loop-through-all-worksheets-in-a-workbook
• Iterate through column K looking for values
  • Iterating through populated rows in Excel using VBA
  • In this example they are looking at column A. You can replace 1 in the sh.Cells(row,1) to sh.Cells(row,11)
• Copy the data to the mastertab
  • Copy from one workbook and paste into another