[F[_2_]]

I have a worksheet in Excel 2010 into which, each day, I paste two
columns from a temporary sheet into columns K and L.

Values from K and L are then extracted into C2, D2, E2 and F2 using


=IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$151),$L$8: $L$151,0))<"00:10",INDEX($K$8:$K$151,MATCH(MAX($L $8:$L$151),$L$8:$L$151,0)),"No
output")

=MAX($L$8:$L$151)

{=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))}

{=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0))}

respectively.

I then copy the contents of C2:F2 and paste them into C4:F4 and then,
the next day, into C5:F5 and so on until the end of the month. The next
month is on a separate sheet.

I've struggled, and failed, to write a macro to automate this. Can
anyone help?

--
F

www.vulcantothesky.org - 2015, the last year to see a Vulcan fly

[isabelle]

hi,

how have you nominated the tabs of each month?

isabelle

Le 2015-07-31 07:40, F a écrit :
I have a worksheet in Excel 2010 into which, each day, I paste two columns from
a temporary sheet into columns K and L.

Values from K and L are then extracted into C2, D2, E2 and F2 using


=IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$151),$L$8: $L$151,0))<"00:10",INDEX($K$8:$K$151,MATCH(MAX($L $8:$L$151),$L$8:$L$151,0)),"No
output")

=MAX($L$8:$L$151)

{=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))}

{=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0))}

respectively.

I then copy the contents of C2:F2 and paste them into C4:F4 and then, the next
day, into C5:F5 and so on until the end of the month. The next month is on a
separate sheet.

I've struggled, and failed, to write a macro to automate this. Can anyone help?

[isabelle]

does the tabs of each month must be created successively?

isabelle

Le 2015-07-31 23:32, isabelle a écrit :
hi,

how have you nominated the tabs of each month?

isabelle

[isabelle]

i assumed that there is a "model" for the months sheet
http://www.cjoint.com/c/EHbeCbeGRma


Sub test()
oTab = "" & Year(Now) & "-" & Format(Month(Now), "00")
oExists = WorksheetExists(oTab)
If Not oExists Then
Sheets("model").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = oTab
LastRow = 4
Else
LastRow = Sheets(oTab).Cells(Rows.Count, 3).End(xlUp).Row + 1
End If
Sheets(oTab).Range(Cells(LastRow, 3), Cells(LastRow, 6)).Value = Sheets("paste
two columns").Range("C2:F2").Value
End Sub

Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name < "")
Err.Clear
End Function

isabelle

[F[_2_]]

Hi

The tab for each month is named after the month: January, February,
March etc.

--
F

www.vulcantothesky.org - 2015, the last year to see a Vulcan fly


On 01/08/2015 04:32, isabelle wrote:
hi,

how have you nominated the tabs of each month?

isabelle

Le 2015-07-31 07:40, F a écrit :
I have a worksheet in Excel 2010 into which, each day, I paste two
columns from
a temporary sheet into columns K and L.

Values from K and L are then extracted into C2, D2, E2 and F2 using


=IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$151),$L$8: $L$151,0))<"00:10",INDEX($K$8:$K$151,MATCH(MAX($L $8:$L$151),$L$8:$L$151,0)),"No

output")

=MAX($L$8:$L$151)

{=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))}

{=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0))}

respectively.

I then copy the contents of C2:F2 and paste them into C4:F4 and then,
the next
day, into C5:F5 and so on until the end of the month. The next month
is on a
separate sheet.

I've struggled, and failed, to write a macro to automate this. Can
anyone help?

[F[_2_]]

Hi

No, each tab is already created. I treat them separately rather than as
being 'connected'.

--
F

www.vulcantothesky.org - 2015, the last year to see a Vulcan fly


On 01/08/2015 04:41, isabelle wrote:
does the tabs of each month must be created successively?

isabelle

Le 2015-07-31 23:32, isabelle a écrit :
hi,

how have you nominated the tabs of each month?

isabelle

[Claus Busch]

Hi,

Am Fri, 31 Jul 2015 12:40:39 +0100 schrieb F:

I've struggled, and failed, to write a macro to automate this. Can
anyone help?

why don't you write the values into C:F? Then you don't have to copy &
paste values. Paste the new data to K:L and then run the macro.

Sub Test()
Dim dest As Range

With ActiveSheet
Set dest = .Cells(Rows.Count, 3).End(xlUp)(2)
dest = Evaluate("=IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$ 151),$L$8:$L$151,0))" _
& "<""00:10"",INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$1 51),$L$8:$L$151,0)),""No output"")")
dest.Offset(, 1) = Evaluate("=MAX($L$8:$L$151)")
dest.Offset(, 2) =
Evaluate("=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))")
dest.Offset(, 3) =
Evaluate("=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0) )")
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[F[_2_]]

On 01/08/2015 10:09, Claus Busch wrote:
Hi,

Am Fri, 31 Jul 2015 12:40:39 +0100 schrieb F:

I've struggled, and failed, to write a macro to automate this. Can
anyone help?

why don't you write the values into C:F? Then you don't have to copy &
paste values. Paste the new data to K:L and then run the macro.

Sub Test()
Dim dest As Range

With ActiveSheet
Set dest = .Cells(Rows.Count, 3).End(xlUp)(2)
dest = Evaluate("=IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$ 151),$L$8:$L$151,0))" _
& "<""00:10"",INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$1 51),$L$8:$L$151,0)),""No output"")")
dest.Offset(, 1) = Evaluate("=MAX($L$8:$L$151)")
dest.Offset(, 2) =
Evaluate("=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))")
dest.Offset(, 3) =
Evaluate("=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0) )")
End With
End Sub


Regards
Claus B.

Hi Claus

Many thanks for that.

It works really well except I had not realised it was relevant to say
that there is data in A36:E40 and so the macro is writing the values
into C41:F41 and below.

Is there a solution which ignores the presence of the data in A36:E40
and so start writing in C4:F4 (the first empty area above A36:E40) and
then subsequent rows?

Regards
--
Frank

[Claus Busch]

Hi Frank,

Am Mon, 3 Aug 2015 10:52:48 +0100 schrieb F:

Is there a solution which ignores the presence of the data in A36:E40
and so start writing in C4:F4 (the first empty area above A36:E40) and
then subsequent rows?

then you have to hardcode the target cell every day.
My suggestion: Delete C4:F40 first

With ActiveSheet
.Range("C4:F40").ClearContents
Set dest = .Cells(Rows.Count, 3).End(xlUp)(2)
dest =
Evaluate("=IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$ 151),$L$8:$L$151,0))"
_
&
"<""00:10"",INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$1 51),$L$8:$L$151,0)),""No
output"")")
dest.Offset(, 1) = Evaluate("=MAX($L$8:$L$151)")
dest.Offset(, 2) =
Evaluate("=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))")
dest.Offset(, 3) =
Evaluate("=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0) )")
End With


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[Claus Busch]

Hi Frank,

Am Mon, 3 Aug 2015 12:07:47 +0200 schrieb Claus Busch:

.Range("C4:F40").ClearContents

ignore the previous post. You cannot delete every time you run the
macro. Delete that range manually before first run.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[Claus Busch]

Hi again,

Am Mon, 3 Aug 2015 10:52:48 +0100 schrieb F:

Is there a solution which ignores the presence of the data in A36:E40
and so start writing in C4:F4 (the first empty area above A36:E40) and
then subsequent rows?

if you run the macro every day you could create the row with the day:
Set dest = .Cells(Day(Date) + 3, 3)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[F[_2_]]

On 03/08/2015 11:20, Claus Busch wrote:
Hi again,

Am Mon, 3 Aug 2015 10:52:48 +0100 schrieb F:

Is there a solution which ignores the presence of the data in A36:E40
and so start writing in C4:F4 (the first empty area above A36:E40) and
then subsequent rows?

if you run the macro every day you could create the row with the day:
Set dest = .Cells(Day(Date) + 3, 3)


Regards
Claus B.

Hi

And thanks again.

Trying hard to educate myself... would
Set dest = Range("C36").End(xlUp)(2)
do what I want?

Regards
--
Frank

[Claus Busch]

Hi Frank,

Am Mon, 3 Aug 2015 12:01:17 +0100 schrieb F:

Trying hard to educate myself... would
Set dest = Range("C36").End(xlUp)(2)
do what I want?

not if you have data in C1:C40.
Then it will find C41


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[[email protected]]

On Friday, July 31, 2015 at 6:42:05 AM UTC-5, F wrote:
I have a worksheet in Excel 2010 into which, each day, I paste two
columns from a temporary sheet into columns K and L.

Values from K and L are then extracted into C2, D2, E2 and F2 using


=IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$151),$L$8: $L$151,0))<"00:10",INDEX($K$8:$K$151,MATCH(MAX($L $8:$L$151),$L$8:$L$151,0)),"No
output")

=MAX($L$8:$L$151)

{=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))}

{=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0))}

respectively.

I then copy the contents of C2:F2 and paste them into C4:F4 and then,
the next day, into C5:F5 and so on until the end of the month. The next
month is on a separate sheet.

I've struggled, and failed, to write a macro to automate this. Can
anyone help?

--
F

www.vulcantothesky.org - 2015, the last year to see a Vulcan fly

I need help!!!!