How to create new workbooks for each variable

Hi I have a panel dataset containing 400 issuers and would like all data for each issuer to be located on its own workbook. My time period runs from 01/01/2000 - 01/01/2012 and therefore my first step (the one I am stuck on) is to create 400 worksheets (say named data1 - data400). The only data I need to initially have on the worksheets is the days traded between 01/01/2000-01/01/2012 which I have. I assume I should open the workbook containing the date file and write a loop that creates each spreadsheet and copies the date column, afterwards I could merge the data using a similar loop. I am just unsure of the syntax for specifically creating new workbooks in a loop. Any suggestions would be greatly appreciated.

Mike

Mike

Do you want to create a workbook containing 400 worksheets, or do you want to create 400 different workbooks? Both are fairly easy and both seem to me to be messy approaches to managing data.

Why not keep all your data on a single worksheet with an issuer identifier column and use filters or some other approach to create output for the different issuers as required?

Peter

Hi Peter,

I would have kept it all on a single worksheet however it would be over 1,100,000 lines, (excel 2010 only is capable of using 1,048,576 lines as far as I know). I would like each issuer to be in a seperate workbook as it should make my work easier down the road. I have searched the internet over the past day and come up with the following code, it works for the first step I mentioned. So I have 400 workbooks each given the name of one issuer, each containing the same date column. I now need to loop through each workbook and merge all data corresponding to the name of the workbook. I'll post what I have below.

Sub Create_many_workbooks_and_fill()
'''use to create all workbooks filled with date column
'''then merge all other data onto date files

    Dim rngName As Range
    Dim wk As Workbook
    Dim i As Integer
    Set rngName = ThisWorkbook.Sheets("Term Spread").Range("f1")  '''fill this range with bond names
   
    ChDir "C:\Users\Mike\Desktop\Database"   '''change directory path to database folder
    dates = ThisWorkbook.Sheets("Term Spread").Range(Cells(3, 1), Cells(3132, 1))

    Application.ScreenUpdating = False
   
    For i = 1 To 400
        Set wk = Workbooks.Add
        Range(Cells(3, 1), Cells(3132, 1)) = dates ''fill with date column
        wk.SaveAs "Bond#" & i & rngName(i * 3)
         Dim WkbkName As Object

          On Error GoTo Close_Error
          Application.ScreenUpdating = False

          For Each WkbkName In Application.Workbooks()
              If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
          Next

          ' If everything runs all right, exit the sub.
          GoTo closed

      ' Error handler.
Close_Error:
          MsgBox Str(Err) & " " & Error()
          Resume Next
closed:
    Next i
   
   

 End Sub