Macro to combine data ranges from multiple tabs

Hello,

I found the below code to combine multiple worksheets into a single worksheet.

Wanted to check if someone can assist with amending the code for me for the following please:

- Copy a particular range from each worksheet e.g. A5 to FU38 rather than the entire worksheet
- Copy only values and formatting as i believe if formulas are also copied over, they may break
- Is it possible to add the sheet name from which the data is copied into column A of the combined worksheet in each row the data is pasted?
- Can i limit the number of worksheets that the data is copied from i.e. either specify a list, provide a number of sheets to copy or perhaps the code can copy all sheets except the last 5 sheets in the workbook. As i want to use this on multiple workbooks, the last option would work best i suppose.
- Finally, if the values in the worksheets change, can i run the macro to refresh the values?


Thanks in advance for your help!

__________________________________________

Please Login or Register  to view this content.

Hi

Please wrap code into proper tags - CODE (see Forum rules #2).

Hello Exce101,

Try the following code:-

Please Login or Register  to view this content.

Firstly, open a new worksheet and name it Sheet List. Next, in A1 of this new sheet, place the heading ShList. From A2 on, place the names of the worksheets that you wish to exclude. Select the list of names including the heading and in the ribbon, select Formulas and, in the Defined Names group, select Define Name from the drop down. A New Name dialogue box will appear which will show you "List" in the name box and the selected range in the "Refers to" box. Click OK. You now have the named range as shown in the code. Whilst the list is still selected, click on the Insert tab and select Table. A Create Table dialogue box will appear showing you the selected range. Make sure that you tick the "my table has headers" box. Click OK. Your named range is now in table format. You can add/remove the relevant sheets as you wish.

The code, when run, will copy the required range from each sheet except the ones noted in the ShList. Make sure that the "Combined" worksheet is placed in the list as well as the Sheet List worksheet

I hope that this helps.

Cheerio,
vcoolio.

crossposted: https://www.mrexcel.com/forum/excel-...-all-tabs.html

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. I have added them for you today. Please observe this requirement in the future as it has disturbed and wasted some of our members time already.)