Same VBA code executed sequentially to each workbook and worksheet

Hi all

tried to find answer in the forum but it seems there is no topic for this.

i have 10 workbooks with different names - each of them contains 1 worksheet with the same name as the workbook. (e.g. report1.xlsx - name of the worksheet - report)
i have a code that transforms the data and copy it to another workbook - at the end the data is collected and transformed into one workbook and worksheet
currently i made the code simple and copied the same code for each workbook and worksheet (all my workbooks are open while i execute code - im trying also to make it open them)

my question is
is there a way to set the workbook name and worksheet name as a variable and to be executed like -

set next workbook name
set next worksheet name

execute code

goback to set next names


after the last workbook quit

You could easily loop through all the open workbooks, excluding the workbook you are copying to.

Please Login or Register  to view this content.

i have tried similar set up of the code but in the begining of the code array with the workbook names and cant seem to be able to connect it with the FOR part

Please Login or Register  to view this content.

Are the workbooks open or not?

In your original post you seemed to indicate all the workbooks you want to get data from are open.

Originally Posted by Norie

Are the workbooks open or not?

In your original post you seemed to indicate all the workbooks you want to get data from are open.

yes they are open but i want to avoid copying other data if there are other workbooks open

Do you have a list of the workbook names you do want to get data from?