Copying worksheets within the same workbook

Ok,

Here is what I am trying to do:

Open a folder and select all excel files in it (done)
Copy all worksheets of each file into my main workbook (done)
Copy all the worksheets of my main workbook (except the one that has the macro button) within the same workbook and clear their contents (done)
Final step:
Select each original worksheet from my workbook (that has data) and run some calculations and put the results into its copy. (not done!)

In effect after I copy the sheets into my workbook my wbook tabs looks like

Sheet1 Sheet2 Console

Console is my main sheet where I will have the macro button

After I copy the worksheets within the same workbook I get something like

Sheet1 Sheet2 Console Sheet1 (2) Sheet2 (2)

That is fine. I proceed on clearing all the contents from Sheet1 (2) and Sheet2 (2).

Next I want to go back to Sheet1, run some calculations that select a specific amount of data (I have the calculation in a macro, that is fine) and put the result in Sheet1 (2). That is where I have the problem. It works for the first worksheet but it will not proceed to the rest. Keep in mind that I may have any number of worksheets, not just two.

I believe the problem is with the "Destination" worksheet and the way I describe it, I think I should create some kind of variable with it but i'm not
sure how.


Here is my code so far. It break on the line in red.

Please Login or Register  to view this content.

Any ideas? Also if I could rename each worksheet when it is copied to something more intelligent, i.e instead of Sheet1 to become Sheet1 (2) to have it like Sheet1_data or something like that I would be gratefull.

K

I have finally solved it!!!

I tried different codes and I made it work. I even managed to make it better and more efficient in the process.

So this is the sequence of things the following macro does:

1) Opens a "file open" window that allows you to select excel files from any folder

2) Create a worksheet on your destination workbook that has the same name as the one in the excel file(s) you open adding the suffix "_data"

3) Filters information from all the opened workbook(s) and dumps the data in your destination worksheet(s)

Please see further comments at the end of this post

Please Login or Register  to view this content.

What I would like from you is the following:

a) since I tried many different approaches I think some of the code in the macro serves no purpose. Any idea which bits I can delete safely?

b) When creating the new worksheet in my destination workbook I do not use a very elegant approach. I basically copy the worksheet from the opened excel file to the destination workbook, rename it by adding the suffix I want and then clear its contents. Any better way to do that (if it's too complex then it's fine to stay with what I've got)

b2) How could I avoid specifying the name of the macro workbook (macro file2.xls) because I would like to rename the file without having to edit the code every time?

c) Any bits you feel I could improve? Any comments?

I'm happy to have solved this myself! yey!!!