#1
|
|||
|
|||
VBA: Creating new Worksheets from two different Workbooks
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 1) that will create as many Worksheets in WorkbookOWN as there are in Workbook1 and 2) that 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 more 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, Johana |
#2
|
||||
|
||||
Hi johanna,
In your 'modul1', try replacing the three 'If strFile <> CStr(False) Then' blocks with one block coded as: Code:
If strFile <> CStr(False) Then Set objWB = Workbooks.Open(strFile) For Each objSh In ThisWorkbook.Worksheets If objSh.Name <> "Übersicht" Then If SheetExist(objSh.Name, objWB) = False Then Set objNewSh = ThisWorkbook.Sheets.Add objNewSh.Name = Split(objWB.Name, ".")(0) End If objSh.Range("g7") = objWB.Sheets(objSh.Name).Range("g7").Value objSh.Range("C12:I28") = objWB.Sheets(objSh.Name).Range("C12:I28").Value objSh.Range("b7") = objWB.Sheets(objSh.Name).Range("b7").Value End If Next objWB.Close False End If Code:
If strFile <> CStr(False) Then Set objWB = Workbooks.Open(strFile) For Each objSh In ThisWorkbook.Worksheets If objSh.Name <> "Übersicht" Then If SheetExist(objSh.Name, objWB) = False Then Set objNewSh = ThisWorkbook.Sheets.Add objNewSh.Name = Split(objWB.Name, ".")(0) End If objSh.Range("p7") = objWB.Sheets(objSh.Name).Range("g7").Value objSh.Range("L12:R28") = objWB.Sheets(objSh.Name).Range("C12:I28").Value End If Next objWB.Close False End If
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Chalenge!.. Working with Connected Workbooks | Spanec | Excel | 1 | 01-13-2012 10:40 AM |
Link multiple excel workbooks to publisher | bandcsaravia | Publisher | 0 | 10-24-2011 09:14 AM |
Combining 2 workbooks into 1 workbook with TWO sheets | dguenther | Excel | 1 | 10-06-2011 03:25 AM |
Excel 2010 Workbooks are opening restored | UnMortal | Excel | 0 | 02-16-2011 05:25 PM |
Page Numbering in Workbooks & Print set up problem | mdouble588 | Excel | 0 | 05-21-2006 10:29 AM |