Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-28-2012, 10:41 PM
Johana Johana is offline VBA: Creating new Worksheets from two different Workbooks Windows 7 64bit VBA: Creating new Worksheets from two different Workbooks Office 2007
Novice
VBA: Creating new Worksheets from two different Workbooks
 
Join Date: Aug 2012
Posts: 1
Johana is on a distinguished road
Default 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
Attached Files
File Type: xlsm Excel Makro.xlsm (78.5 KB, 4 views)
File Type: xlsx Test.xlsx (173.2 KB, 2 views)
Reply With Quote
  #2  
Old 09-07-2012, 09:30 PM
macropod's Avatar
macropod macropod is online now VBA: Creating new Worksheets from two different Workbooks Windows 7 64bit VBA: Creating new Worksheets from two different Workbooks Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,234
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA: Creating new Worksheets from two different Workbooks 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
VBA: Creating new Worksheets from two different Workbooks 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:37 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft