Save as Macro

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]

[vba]

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

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

Look at SaveCopyAs in VBA help.

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

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.

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?

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

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.

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]

[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.

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]