CODEWITHSUNDEEP
vbaexcel
AJ Eman
AJ Eman

Reputation: 11

Create, name, and populate new workbook with data

I have an Excel sheet with two columns of data. Column A has the desired new workbook names and Column B's adjacent cell has the data I want in the new work book.

So if 032411 is in column A and 50 is in column B then the macro would create a new workbook named 032411 and 50 would be in cell A1 of that workbook.

Upvotes: 0

Views: 8292

Answers (1)

Tiago Cardoso
Tiago Cardoso

Reputation: 2107

You can use this:

Sub CreateBooks()

    Dim oCell As Excel.Range
    Dim oWorkbook As Excel.Workbook

    'Added to avoid messages asking to confirm overwriting
    '   previous existent files with same name

    Application.DisplayAlerts = False

    For Each oCell In Range("A:A")

        If oCell.Value = "" Then Exit For

        Set oWorkbook = Workbooks.Add

        oWorkbook.Sheets(1).Cells(1, 1).Value = oCell.Offset(0, 1).Value

        'If the cell value contains only the file name (instead of the whole path
        '   the file needs to be saved) it will save into MyDocuments folder
        oWorkbook.Close True, oCell.Value

    Next oCell

    Application.DisplayAlerts = True

End Sub

In case you have several files to generate, you can alternatively use the application.ScreenUpdating = False.

Upvotes: 2

Related Questions