VBA: Creating new Worksheets from two different Workbooks

macropod · #1 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