VBA: Creating new Worksheets from two different Workbooks

Johana · #1 08-28-2012, 10:41 PM

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

macropod · #2 09-07-2012, 09:30 PM

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

In your 'modul2', try replacing the two '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("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

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