-1

I encounter a problem that I can’t solve. Here it is :

I want to copy certain worksheets contained in certain workbooks and paste it all in a single new workbook.

More specifically, I have a Folder called « Fonds » which contain 20 workbooks, each one having the same structure : They are all called « 01082014_FONDS », 01082014 changing for each workbook and being a day of the month. In each of those workbooks, there is a worksheet called « Portfolio » and another one called « Disponibilités ». I wanna copy those 2 worksheets (there are others but I want to copy only those ones) and paste it in a new workbook.

A the end, I have 40 sheets in a single workbooks, called « Portfolio 1 », « Disponibilités 1 », « Portfolio 2 », « Disponibilités 2 »…

Anyone can help me on this ? Thank you very much !

Pierre Delarroqua
  • 33
  • 4

2 Answers2

1

You'll need to do the following in VBA:

  1. Loop through files in a folder Loop through files in a folder using VBA?
  2. Find if the word "Fonds" is in the file name: Check if a string contains another string
  3. Open the workbook that you find: Set wb = Workbooks.open(filename)
  4. Loop through the worksheets in the workbook: Loop through Excel Sheets
  5. Find if the word "Portfolio" or "Disponibilités" is in the worksheet's name: Check if a string contains another string
  6. Rename the worksheet you find: http://www.mrexcel.com/forum/excel-questions/72647-any-way-rename-sheet-visual-basic-applications.html
  7. Copy the worksheet to the current workbook: Copy an entire worksheet to a new worksheet in Excel 2010
  8. Close the workbook: wb.close SaveChanges:=false
Community
  • 1
  • 1
JNevill
  • 46,980
  • 4
  • 38
  • 63
0

To help you, here is the first point...

first point :

Function s()

MyPath = "D:\FOLDER\TEST\*.xls"   ' Set the path.
MyName = Dir(MyPath)   ' Retrieve the first entry.
Do While MyName <> ""   ' Start the loop.
   Call MsgBox(MyName)
   MyName = Dir()   ' Get next entry.
Loop

End Function

Derf
  • 1