Save as Macro

[maninjapan]

I found the following simple macro to save as a new file. How can I add to this to specify the folder and new name to save the file as? for example save to C://TestFolder/SavehereFolder

Thanks in advance.

[VBA]
Public Sub SaveAsA1() ThisFile = Range("A1").Value ActiveWorkbook.SaveAs Filename:=ThisFileEnd Sub[/VBA]

[Bob Phillips]

[vba]

Public Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:="C:\test\subtest\" & ThisFile
End Sub [/vba]

[maninjapan]

Thanks Xld, Works exactly as it should, but Ive just figured out its not what I need. I'm using RTD links to retrieve data and need to save the data at certain time periods. I guess I need the macro to copy/paste special to a new workbook or CSV file then save that worksheet in the specified folder. Obviously mroe complicated than I started with, but if there are any examples, I would be happy to take a look at them first.

Thanks

[Bob Phillips]

Look at SaveCopyAs in VBA help.

[maninjapan]

I was able to adapt a macro I found elsewhere on this thread. By itself it seems to work as I need it to, so I was looking to add an If statement. I need it to execute at a fixed time. I have a running clock in my sheet (K4) and I want it to execute at the time entered in cell (K5). I right clicked on the sheet tab and pasted the code through there, but it doesnt seem to be executing. Have I missed something here?

[VBA]

Private Sub CopySheetAll()
Dim wks As Worksheet
Dim wb As Workbook
Dim Path As String
If Range("K4").Value = Range("K5").Value Then
Set wks = ThisWorkbook.Worksheets("Data")
Set wb = Workbooks.Add(xlWorksheet)
wks.Copy After:=wb.Worksheets(1)
Application.DisplayAlerts = False
wb.Worksheets(1).Delete
Application.DisplayAlerts = True

Set wks = wb.Worksheets(1)

'unprotect or protect w/UserInterfaceOnly here

wks.UsedRange.Value = wks.UsedRange.Value

wb.SaveAs Filename:="C:\test\" & Range("K2").Value
End If

End Sub
[/VBA]

Thanks

[BrianMH]

You need to run it at a particular time? Check out the help for OnTime.
Unless your macro above is constantly running in a loop and recalculating it won't ever pick up that its the time you want.

[maninjapan]

I tried the following:

pasted in This Workbook

[VBA]
Private Sub SaveSheet()

Application.OnTime TimeValue("00:31:00"), "CopySheetAll"
EndSub[/VBA]

Pasted in a module:

[VBA]
Private Sub CopySheetAll()
Dim wks As Worksheet
Dim wb As Workbook
Dim Path As String
Set wks = ThisWorkbook.Worksheets("Data")
Set wb = Workbooks.Add(xlWorksheet)
wks.Copy After:=wb.Worksheets(1)
Application.DisplayAlerts = False
wb.Worksheets(1).Delete
Application.DisplayAlerts = True

Set wks = wb.Worksheets(1)

'unprotect or protect w/UserInterfaceOnly here

wks.UsedRange.Value = wks.UsedRange.Value

wb.SaveAs Filename:="C:\test\" & Range("K2").Value

End Sub
[/VBA]



I've changed the time a few times trying to get the Macro to execute, but not having any luck. (my PC clock is set to local Japanese time so time is current). What am I missing here?

[Simon Lloyd]

Check out Chip's site for the proper formation http://www.cpearson.com/excel/OnTime.aspx

[maninjapan]

Thanks for the tip simon, I had a look and it shows an example of a timer every N seconds or minutes, but not just at a fixed time each day.

[Simon Lloyd]

You have to run the timer every n minutes or hours and check whether the time at runtime is >= a static time.

Straight out of VBA help files:
[vba]
Dim MyTime
MyTime = #4:35:17 PM#
' Assign a time.Time = MyTime
' Set system time to MyTime.[/vba]

[BrianMH]

[vba]

Private Sub Workbook_Open()

Application.OnTime TimeValue("18:15:00"), "CopySheetAll", , True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnTime dTime, "CopySheetAll", , False

End Sub

[/vba]
put that in the workbook module.

and the below in a standard module

[vba]Sub CopySheetAll()
Dim wks As Worksheet
Dim wb As Workbook
Dim Path As String
Set wks = ThisWorkbook.Worksheets("Data")
Set wb = Workbooks.Add(xlWorksheet)
wks.Copy After:=wb.Worksheets(1)
Application.DisplayAlerts = False
wb.Worksheets(1).Delete
Application.DisplayAlerts = True

Set wks = wb.Worksheets(1)

'unprotect or protect w/UserInterfaceOnly here

wks.UsedRange.Value = wks.UsedRange.Value

wb.SaveAs Filename:="C:\test\" & Range("K2").Value

Application.OnTime TimeValue("18:15:00"), "CopySheetAll"

End Sub
[/vba]

adjust the times as necessary of course and you will either need to run your copysheetall macro once or close and reopen the workbook.

[maninjapan]

thanks, it seems to be working some of the time, but everytime I shut down excel I get a runtime error '1004' and it highlights the following line of code.

[VBA]
Application.OnTime dTime, "CopySheetAll", , False
[/VBA]