macro closing when it should'nt

[dmheller]

I have this and it runs when the workbook is open but it closes after it runs. Any help would be great thanks.
Private Sub Workbook_open()

Dim path As String
Dim filename1 As String

' copy and paste
Sheets("Sheet1").Cells.Copy
Sheets("Sheet1").Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'end copy and paste

path = "C:\Users\hellerda\Desktop\test"
filename1 = Range("C9").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path & filename1 & "nox.xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True


End Sub

 

[Logit]

Re: macro closing when it shoundt

.
Aside from when the workbook is opened .... at what other time/s do you want the macro to run ?

 

[dmheller]

Re: macro closing when it shoundt

I guess really only when someone wants to run it. In the pig picture, I am wanting to have task scheduler open it, then I have to put a hold on it before it runs then it runs and I would like the new file that it makes emailed out everyday. I guess I would like a command button too for email the file when someone wants an update.

 

[Logit]

Re: macro closing when it shoundt

.
To run the macro when you want, after the workbook opens ...

Paste this macro in the ThisWorkbook module :

Option Explicit


Private Sub Workbook_Open()
    cpyPaste
End Sub

Paste this macro in a regular module :

Option Explicit


Sub cpyPaste()
Dim path As String
Dim filename1 As String


' copy and paste
Sheets("Sheet1").Cells.Copy
Sheets("Sheet1").Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False


'end copy and paste


path = "C:\Users\hellerda\Desktop\test"
filename1 = Range("C9").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path & filename1 & "nox.xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
End Sub

Then place a CommandButton on a worksheet and connect it to the cpyPaste macro.

 

[dmheller]

Re: macro closing when it shoundt

Ok, sorry to be new at this VBA but how would I connect the commabdbutton to the cpypaste macro? I know it will give me a new sub and a new end sub. do I just delete that or what would it be.

 

[Logit]

Re: macro closing when it shoundt

[h=2]Insert VBA code to Excel Workbook[/h][FONT=&quot]For this example, we are going to use a VBA macro to remove line breaks from the current worksheet.[/FONT]

1. Open your workbook in Excel.
2. Press Alt + F11 to open Visual Basic Editor (VBE).
   
   
3. Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu.
   
   
4. Copy the VBA code (from a web-page etc.) and paste it to the right pane of the VBA editor ("Module1" window).
   
   
5. After the above, close the VBE and go back to the worksheet. Insert a CommandButton on the sheet. Once you place the button on the sheet
6. a small window will open with all macros listed. Click on the macro you just pasted then click OK. You are done.
7. When you save the workbook with the macro, be sure to save it as 'macro enabled'.
8. Resource : https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/