PDA

View Full Version : SCHEDULE help please



formratings
05-02-2015, 02:29 AM
sorry if this is simple guys but I am very new to this never done it before ...and been left in the lurch by a lady who was helping me with this :( :( :crying:
I have a workbook that opens at 11.00 and when I save needs to save as workbook name date and cell data is this possible
workbook name is Btw
date
cell =races!$AB$40= e.g 17%
eg Btw2ndmay17%

currently I have


Private Sub Workbook_Open()


Dim data
Dim entervalues_sh As Worksheet, lrow As Long


If ThisWorkbook.Name = "Btw.xlsm" Then


If Sheets("nofrillsbetfair").Range("d13") <> Date Then
If Time > TimeSerial(11, 0, 0) And Time <= TimeSerial(12, 30, 0) Then Application.Run "sheet2.CommandButton21_Click" Else Application.OnTime TimeSerial(11, 0, 0), "Btw.xlsm!Sheet2.CommandButton21_Click"
With Sheets("nofrillsbetfair")
.Unprotect "daveGood"
.Range("d13") = Date
.Protect "daveGood"
End With
ThisWorkbook.Save
End If



also is it possible to get the book to close automatically after last cell data in a column
eg starting in ='enter values'!$W$2 and going down to max of ='enter values'!$W$43 I want it to close 30 mins after time in last populated w column which varies every day
and save as above

thanks in advance :):)

Bob Phillips
05-02-2015, 08:49 AM
Private Sub Workbook_Open()

With Me

If .Worksheets("nofrillsbetfair").Range("D13").Value <> Date Then

If Time > TimeSerial(11, 0, 0) And Time <= TimeSerial(12, 30, 0) Then

Application.Run "Sheet2.CommandButton21_Click"
Else

Application.OnTime TimeSerial(11, 0, 0), "Btw.xlsm!Sheet2.CommandButton21_Click"
End If

With .Worksheets("nofrillsbetfair")

.Unprotect "daveGood"
.Range("d13") = Date
.Protect "daveGood"
End With

.SaveAs Filename:=Left$(.Name, InStrRev(.Name, ".") - 1) & Format(Date, "yyyymmdd") & .Worksheets("Races").Range("AB40").Value
End If
End With
End Sub

formratings
05-02-2015, 12:05 PM
hi sorry as I said I have never done this will you put it in correct place for me in this full code I am not sure exactly where I put the new code



Private Sub Workbook_Open()
Dim data
Dim entervalues_sh As Worksheet, lrow As Long
If ThisWorkbook.Name = "Btw.xlsm" Then
If Sheets("nofrillsbetfair").Range("d13") <> Date Then
If Time > TimeSerial(11, 30, 0) And Time <= TimeSerial(12, 30, 0) Then Application.Run "sheet2.CommandButton21_Click" Else Application.OnTime TimeSerial(11, 0, 0), "Btw.xlsm!Sheet2.CommandButton21_Click"
With Sheets("nofrillsbetfair")
.Unprotect "daveGood"
.Range("d13") = Date
.Protect "daveGood"
End With
ThisWorkbook.Save
End If
End If
Set entervalues_sh = Sheets("enter values")
lrow = entervalues_sh.Cells(Rows.Count, "w").End(xlUp).Row
If lrow = 1 Then Exit Sub
data = entervalues_sh.Range("w1:w" & lrow)
For i = 2 To lrow
If data(i, 1) <> "" Then
temp = Trim(data(i, 1))
If Len(temp) = 2 Then
itime = TimeSerial(temp, 0, 0)
ElseIf Len(temp) <> 5 Then
arr = Split(temp, ".")
itime = TimeSerial(arr(0), arr(1) * 10, 0)
Else
arr = Split(temp, ".")
itime = TimeSerial(arr(0), arr(1), 0)
End If
Application.OnTime DateAdd("n", -IIf(Sheets("nofrillsbetfair").Range("f13") = 0, 4, Sheets("nofrillsbetfair").Range("f13")), itime), "Btw.xlsm!Sheet2.CommandButton22_Click"
End If
Next
End Sub









Private Sub Workbook_Open()

With Me

If .Worksheets("nofrillsbetfair").Range("D13").Value <> Date Then

If Time > TimeSerial(11, 0, 0) And Time <= TimeSerial(12, 30, 0) Then

Application.Run "Sheet2.CommandButton21_Click"
Else

Application.OnTime TimeSerial(11, 0, 0), "Btw.xlsm!Sheet2.CommandButton21_Click"
End If

With .Worksheets("nofrillsbetfair")

.Unprotect "daveGood"
.Range("d13") = Date
.Protect "daveGood"
End With

.SaveAs Filename:=Left$(.Name, InStrRev(.Name, ".") - 1) & Format(Date, "yyyymmdd") & .Worksheets("Races").Range("AB40").Value
End If
End With
End Sub

formratings
05-02-2015, 12:46 PM
when I tried it I got compile error Invalid or unqualified reference on the first .Name
.SaveAs Filename:=Left$(.Name, InStrRev(.Name, ".") - 1) & Format(Date, "yyyymmdd") & .Worksheets("Races").Range("AB40").Value
End If

Bob Phillips
05-02-2015, 03:27 PM
Just replace your procedure with mine

formratings
05-02-2015, 03:42 PM
thanks for your help but sorry I did say I've not done this before and just not sure where to put with the End if and End with etc



Just replace your procedure with mine

formratings
05-04-2015, 01:24 AM
anybody please, just not sure exactly where to paste it in between what end if etc
and got the error
compile error Invalid or unqualified reference on the first .Name

.SaveAs Filename:=Left$(.Name, InStrRev(.Name, ".") - 1) & Format(Date, "yyyymmdd") & .Worksheets("Races").Range("AB40").Value
End If

formratings
05-04-2015, 01:29 AM
this is what I hope is correct :dunno


Private Sub Workbook_Open()
With Me
If .Worksheets("nofrillsbetfair").Range("D13").Value <> Date Then
If Time > TimeSerial(11, 0, 0) And Time <= TimeSerial(12, 30, 0) Then
Application.Run "Sheet2.CommandButton21_Click"
Else
Application.OnTime TimeSerial(11, 0, 0), "Btw.xlsm!Sheet2.CommandButton21_Click"
End If
With .Worksheets("nofrillsbetfair")
.Unprotect "daveGood"
.Range("d13") = Date
.Protect "daveGood"
End With
.SaveAs Filename:=Left$(.Name, InStrRev(.Name, ".") - 1) & Format(Date, "yyyymmdd") & .Worksheets("Races").Range("AB40").Value
End If
End With
End Sub
Set entervalues_sh = Sheets("enter values")
lrow = entervalues_sh.Cells(Rows.Count, "w").End(xlUp).Row
If lrow = 1 Then Exit Sub
data = entervalues_sh.Range("w1:w" & lrow)
For i = 2 To lrow
If data(i, 1) <> "" Then
temp = Trim(data(i, 1))
If Len(temp) = 2 Then
itime = TimeSerial(temp, 0, 0)
ElseIf Len(temp) <> 5 Then
arr = Split(temp, ".")
itime = TimeSerial(arr(0), arr(1) * 10, 0)
Else
arr = Split(temp, ".")
itime = TimeSerial(arr(0), arr(1), 0)
End If
Application.OnTime DateAdd("n", -IIf(Sheets("nofrillsbetfair").Range("f13") = 0, 4, Sheets("nofrillsbetfair").Range("f13")), itime), "Btw.xlsm!Sheet2.CommandButton22_Click"
End If
Next
End Sub

this is the bit I was unsure about

End If
End With
End Sub

and got the error here on the .name part of the formula
.SaveAs Filename:=Left$(.Name