Open 1 WorkBook with 2 WorkSheets from TextBoxes

[Andresleo47]

Hi Community! I am trying to open one Workbook with the 2 TimeSheets in the attached TextBoxes.

This is the code I have for now, but it's giving me an error:

Sub CommandButton1_Click()

Workbooks.Add.SaveAs Filename:="ThisWeek.xlsx"
ActiveWorkbook.Worksheets.Add Count:=1

With Sheets("Sheet1")
Workbooks.Open .TextBox1.Text
End With
With Sheets("Sheet2")
Workbooks.Open .TextBox2.Text
End With
End Sub


Any ideas would be appreciated!!

 

[johnnyL]

How about this:

Private Sub CommandButton1_Click()
'
    Dim ClosedWorkbookSheetName As String
    Dim FileExtention           As String
    Dim NewWorkbookName         As String
    Dim SheetWithTextBoxes      As String
    Dim ClosedSourceWorkbook    As Workbook
'
    FileExtention = ".xlsx"                                                                 ' <--- Set this to the file extention of workbook to be created
    NewWorkbookName = "ThisWeek"                                                            ' <--- Set this to the name of the workbook to be created
    ClosedWorkbookSheetName = "report"                                                      ' <--- Set this to the sheet name in the closed workbook
    SheetWithTextBoxes = "Import Files"                                                     ' <--- Set this to the sheet name that contains the TextBoxes
'
    Workbooks.Add.SaveAs Filename:=NewWorkbookName & FileExtention                          ' Create/Name/save New Workbook
'
    ThisWorkbook.Activate                                                                   ' Reactivate this workbook
'
    Application.ScreenUpdating = False                                                      ' Turn ScreenUpdating off
'
    Set ClosedSourceWorkbook = Workbooks.Open(Sheets(SheetWithTextBoxes).TextBox2.Value)    ' Get value from TextBox to open the source file
'
'   Copy sheet to beginning of the newly created workbook
    ClosedSourceWorkbook.Sheets(ClosedWorkbookSheetName).Copy Before:=Workbooks(NewWorkbookName & FileExtention).Sheets(1)
    ClosedSourceWorkbook.Close SaveChanges:=False                                           ' Close the source workbook
'
    ThisWorkbook.Activate                                                                   ' Reactivate this workbook
'
    Set ClosedSourceWorkbook = Workbooks.Open(Sheets(SheetWithTextBoxes).TextBox1.Value)    ' Get value from TextBox to open the source file
'
'   Copy sheet to beginning of the newly created workbook
    ClosedSourceWorkbook.Sheets(ClosedWorkbookSheetName).Copy Before:=Workbooks(NewWorkbookName & FileExtention).Sheets(1)
    ClosedSourceWorkbook.Close SaveChanges:=False                                           ' Close the source workbook
'
    Application.DisplayAlerts = False                                                       ' Turn off DisplayAlerts to avoid sheet deletion warning
    Sheets("Sheet1").Delete                                                                 ' Delete Sheet1 from the newly created workbook
    Application.DisplayAlerts = True                                                        ' Turn DisplayAlerts back on
'
    ActiveWorkbook.Close True                                                               ' Save newly created file and close
'
    Application.ScreenUpdating = True                                                       ' Turn ScreenUpdating back on
End
End Sub

 

[Andresleo47]

Hi, thanks so much! This definitely worked.

Do you think I can keep the resultant file open? It does what it's required, but when the macro's done it closes the file. Much appreciated!!

 

[johnnyL]

Glad to help @Andresleo47.

Did you read the comments I put in on the right side of the lines of code? If you do, you should be able to figure out which line of code closes the result file. Then you can disable/delete that line.

If you can't figure it out, lemme know.

 

[Andresleo47]

Glad to help @Andresleo47.

Did you read the comments I put in on the right side of the lines of code? If you do, you should be able to figure out which line of code closes the result file. Then you can disable/delete that line.

If you can't figure it out, lemme know.

Hi, thanks, I'm assuming it's this one in the attached?

If I change it to False, it keeps closing the file. Not a huge deal, though.

Thanks,

Andres

 

[johnnyL]

Hi, thanks, I'm assuming it's this one in the attached?

If I change it to False, it keeps closing the file. Not a huge deal, though.

You are correct! That is the line. That line is going to close the file, the true/false is the answer to if you want to save the file prior to closing. It will close the file regardless.

You can disable that line by putting an apostrophe at the beginning of that line, or you can delete the line. Those two options, apostrophe or deletion, will keep the file open for you.

 

[Andresleo47]

Oh cool, thanks...But it will save the file anyway, right?

Thanks,

Andres

 

[johnnyL]

If you want to save it at that point without closing it, replace:

    ActiveWorkbook.Close True                                                               ' Save newly created file and close

with:

   ActiveWorkbook.Save

 

[Andresleo47]

Thanks so much for everything. Your help, support, patience and advice is absolutely appreciated.

Take care, and Happy holidays!