Opening and Saving new Workbooks - VBA

Question

So I know there have been questions on this before, but none seem to explicitly solve the problems I'm having. Effectively what I'm trying to do is create a new workbook, copy and paste data into it, and then save that new workbook under a new filename. No matter what I do, I seem to get various types of error messages.

Here is my code. Any help is very appreciated!

Private Sub DoStuff()

CurrentFile = "June_Files_macros_new.xlsm"
NewFile = "Train10_June01.xls"

Workbooks.Add


'Save New Workbook
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & NewFile

For i = 2 To 55 
    If Cells(i, 3) = Cells(i - 1, 3) And Cells(i, 13) = Cells(i - 1, 13) Then
            Workbooks(CurrentFile).Worksheets("Sheet1").Rows(i).Copy _
            Workbooks(NewFile).Worksheets("Sheet1").Rows(i)
    Else: Workbooks(NewFile).SaveAs ThisWorkbook.Path & "\" & "New_Name"
    End If    
Next i

End Sub

It seems to me that the "New_Name" is causing all my problems, but I'm open to changing anything that will allow this to work.

Thanks so much! Zach

ps I'm relatively new to VBA so please try to keep any explanations somewhat simple!

What error are you getting? If you hit `Debug` is it highlighting that `Else: ...` row? Also, is it correctly saving as `newFile`, just not `"New_Name"`? — BruceWayne, Jul 26 '17 at 14:49

Rafael Matos · Answer 1 · 2017-07-26T15:19:28.333

Try this:

Private Sub DoStuff()
    Dim CurrentFile As String
    Dim NewFile As String
    Dim i As Long
    Dim wb As Workbook

    CurrentFile = "June_Files_macros_new.xlsm"
    NewFile = "Train10_June01.xls"

    Set wb = Workbooks.Add
    wb.SaveAs Workbooks(CurrentFile).Path & "\" & NewFile

    For i = 2 To 55
        If Cells(i, 3) = Cells(i - 1, 3) And Cells(i, 13) = Cells(i - 1, 13) Then
            Workbooks(CurrentFile).Sheets("Sheet1").Rows(i).Copy Workbooks(NewFile).Worksheets("Sheet1").Rows(i)
        Else
            Set wb = Workbooks(NewFile)
            wb.SaveAs Workbooks(CurrentFile).Path & "\" & "New_name.xls"
            Exit For
        End If
    Next i

 End Sub

I put this block:

Else
    Set wb = Workbooks(NewFile)
    wb.SaveAs Workbooks(CurrentFile).Path & "\" & "New_name.xls"
    Exit For

Because every time the condition in your If gives a false response, it will try to save the Workbooks(NewFile) with the same name "New_name.xls" and this will give an error, since the Excel cannot save files with the same name.

But I'm not sure what you've wanted with this Else condition.

This is an improvement on my script... but for some reason, it fails to copy and paste the data. As in, I open up both Train10_June1 and New_name and neither have any of the data. I know the for loop and the if statement are working because they were working before... — Zach Schenk, Jul 26 '17 at 15:57
You need to clarify what you want. In your code, for the "New_name.xls" archive, your If-Else statement does nothing, but save the Workbooks(NewFile) with a new name. If you want to copy to both Excel files, you need to check again your If-Else statement. — Rafael Matos, Jul 26 '17 at 16:22

score 0 · Answer 2 · answered Jul 27 '17 at 21:15

With your help, I managed to create something that did what I wanted to. Thanks so much!!!

Private Sub DoStuff()

Application.DisplayAlerts = False

'Create New Workbook

Dim Count As Integer

CurrentFile = "June_Files_macros_new.xlsm"
NewFile = "Train" & CStr(Cells(2, 13)) & "_" & CStr(Cells(2, 3)) & ".xls"

Workbooks.Add


'Save New Workbook
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & NewFile

'Select top row of data and insert into spreadsheed!!!!!
Workbooks(CurrentFile).Worksheets("Sheet1").Rows(2).Copy
Workbooks(NewFile).Worksheets("Sheet1").Rows(2).PasteSpecial xlPasteValues


Count = 3



For i = 3 To 12802

'if Date and Train Number are equal, Then copy and paste the i th row
'else, save new file, create another new file, save

    If Cells(i, 3) = Cells(i - 1, 3) And Cells(i, 13) = Cells(i - 1, 13) Then
            Workbooks(CurrentFile).Worksheets("Sheet1").Rows(i).Copy
            Workbooks(NewFile).Worksheets("Sheet1").Rows(Count).PasteSpecial xlPasteValues
            Count = Count + 1

    Else: Workbooks(CurrentFile).Worksheets("Sheet1").Rows(1).Copy
          Workbooks(NewFile).Worksheets("Sheet1").Rows(1).PasteSpecial xlPasteValues
          Workbooks(NewFile).SaveAs ThisWorkbook.Path & "\" & "Train" & CStr(Cells(i - 1, 13)) & "_" & CStr(Cells(i - 1, 3)) & ".xls"
          Workbooks(NewFile).Close

          Workbooks.Add
          NewFile = "Train" & CStr(Cells(i, 13)) & "_" & CStr(Cells(i, 3)) & ".xls"
          ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & NewFile

          Workbooks(CurrentFile).Worksheets("Sheet1").Rows(i).Copy
          Workbooks(NewFile).Worksheets("Sheet1").Rows(2).PasteSpecial xlPasteValues

          Count = 3
   End If

Next i

Workbooks(CurrentFile).Worksheets("Sheet1").Rows(1).Copy
Workbooks(NewFile).Worksheets("Sheet1").Rows(1).PasteSpecial xlPasteValues

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & NewFile

Workbooks(NewFile).Close

That `:` token is not needed and will eventually cause you trouble. Keep the `Else` token on its own line of code, with all statements of that "else" branch neatly indented underneath like you have the rest of them. Forget the `:` instructions separator even exists, it's never needed, and makes conditional expressions potentially ambiguous. Also consider declaring `Workbook` and `Worksheet` variables as needed, instead of constantly dereferencing them from a `Workbooks` or `Worksheets` collection, and use a 32-bit `Long` instead of a 16-bit `Integer`, which overflows at 32,768. — Mathieu Guindon, Jul 07 '22 at 22:19

Opening and Saving new Workbooks - VBA

2 Answers2

Linked