CODEWITHSUNDEEP
excelvbamacros
Pawel
Pawel

Reputation: 555

create multiple workbooks in certain folder location

Lets say I want to create 5 workbooks in certain location. I was trying to use this code but it doesn't work. (run-time error, automation error), what is wrong with it?

Sub blabal()

Dim wbk As Workbook
Dim i As Integer
i = 1
Set wbk = Workbook.Add

Do Until i = 5
    wbk.SaveAs "C:\Users\User1\Desktop\abc\" & i
    wbk.Close
    i = i + 1
Loop

End Sub

Upvotes: 0

Views: 38

Answers (1)

luke_t
luke_t

Reputation: 2985

Set wbk = Workbooks.Add

You need to specify Workbooks instead of Workbook.

Workbooks is a collection object. You're adding a Workbook to the collection of Workbooks.

You also need to change the order of your code, so you're either not closing the workbook each time, or so that you are closing the workbook, but you're then adding a new workbook (which doesn't really make much sense, but I'll show an example anyway).

Set wbk = Workbooks.Add

Do Until i = 5
    wbk.SaveAs "C:\Users\User1\Desktop\abc\" & i
    i = i + 1
Loop

Or

Do Until i = 5
    Set wbk = Workbooks.Add
    wbk.SaveAs "F:\" & i
    wbk.Close
    i = i + 1
Loop

Upvotes: 2

Related Questions