Creating new Worksheets from two different Workbooks

[BenK]

--------------------------------------------------------------------------------

Hello everybody!!

I am struggling with VBA as I have not much experience with it and I hope that one of you might be able to help me

Once a month I receive two separate workbooks (let’s call them workbook 1 and workbook2) which I need to analyse. In both of the workbooks there are about 30 worksheets which are identically drafted. To do my evaluation I created another workbook (let’s call it WorkbookOWN). I was able to create 2 Modules in WorkbookOWN that allow me to get the Data I need from workbook 1 and 2 and plug it together in WorkbookOWN.


However, my code is pretty simple and it works only for Sheets that are named the same in both files (for instance if Sheet 1 in workbook 1 is called USA and if there is a Sheet called USA in WorkbookOWN as well, it works). What I am trying to do is to create a macro that a) will create as many Worksheets in WorkbookOWN as there are in Workbook1 and b) names the Sheets of WorkbookOwn after Workbook1. The name of the Sheet is always in cell “B7” of the respective Worksheet. I guess the tricky part is that in 95 % of the cases, Workbook 1 and Workbook 2 have the same amount of worksheets and are named the same, but sometimes there are 1 or 2 additional Worksheets in Workbook 2.

I attached the file “test” (which is workbook 1) and the file “Excel Macro” (which is WorkbookOWN) to show you what I mean.

I would highly appreciate all kind of help because I tried different things but actually I’m lost!!

Thank you in advance for your help!

Regards,
Ben

Test.xlsxExcel Makro.xlsm

[bongaan]

insert this code after your strFile = Application.GetOpenFilename("Excel Dateien (*.xls; *.xlsx; *.xlsm)," & _
"*.xls; *.xlsx; *.xlsm")


Remember to Dim ownWB as workbook.

This will loop through your objWB sheets and if the sheet does not exist in your ownWB then it will create it

Please Login or Register  to view this content.

[BenK]

Hey bongaan!

First of all, thank you very much for you help!!

The loop works perfect, thank you. However, your code gets the data I need of every worksheet (which is a good thing ) but plugs the data into new worksheets.
Is it possible to adjust your code in order create new worksheets which are all drafted in same (as for instance "Land A" or let's say WorkbookOWN Worksheet 1)?

And a final question, if I may, is it possible not to create new worksheets for hidden worksheets?

Once again, thanks a lot for your great help!

Regards
Ben

[bongaan]

Replace the original code I gave you with the following. There is just one possible issue, I get the formatting for the new sheets from "Land A". So if it happens in the future that you don't have a "Land A" sheet anymore, then you will need to change this code. Then choose another sheet name.

Please Login or Register  to view this content.

[BenK]

Bongaan, seriously, thank you so much!! I spent so many frustrating hours working on that issue... You really were a great help!

Thank you again and take care!!

[Cutter]

@ BenK

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[BenK]

Hello Cutter,

Oh sorry, I didn't know about that. Sorry for the inconvenience. Next time I will do it of course!!

Thank you cutter for the heads-up.

Regards
Ben

[bongaan]

Bongaan, seriously, thank you so much!! I spent so many frustrating hours working on that issue... You really were a great help!

Thank you again and take care!!

Your welcome!