Calculating and copying data into new sheet

[scottwilsonx]

Thank you very much for your help and the code. Works perfectly.
I can see how the columns are calculated in the export page - ver
impressive.

I am perhaps asking the impossible here but here goes...

I see in your formula you are looking for an output in each chang
within column A,
is there anyway you can say in the VBA that you only want to repor
each change in column A when, for example column B is 1/12/2003 ?

Many thanks again,

Scott

 

[Bernie Deitrick]

Scott,

Not sure what you mean by change (or report), but you can use something like
the below. Note that the date in the line

If CLng(myCell(1, 2).Value) = CLng(DateValue("12/1/2003")) Then

must be entered as a US-style date: mm/dd/yyyy, not dd/mm/yyyy - that's just
VBA.

HTH,
Bernie
MS Excel MVP

Sub TryNow2()
Dim myCell As Range
Dim myRange As Range
Dim i As Long
Dim mySht As Worksheet
Dim DataSht As Worksheet

Set DataSht = ActiveSheet

On Error Resume Next
Worksheets("New Data Set").Delete

Set mySht = Worksheets.Add
mySht.Name = "New Data Set"

For Each myCell In DataSht.Range(DataSht.Range("A2"), _
DataSht.Range("A65536").End(xlUp))
If CLng(myCell(1, 2).Value) = CLng(DateValue("12/1/2003")) Then
For i = CLng(myCell(1, 2).Value) To CLng(myCell(1, 3).Value)
mySht.Range("A65536").End(xlUp)(2).Value = myCell.Value
mySht.Range("B65536").End(xlUp)(2).Value = i
mySht.Range("C65536").End(xlUp)(2).Value = myCell(1, 4).Value
mySht.Range("D65536").End(xlUp)(2).Value = myCell(1, 5).Value
Next i
End If
Next myCell

mySht.Range("B:B").NumberFormat = "dd/mm/yyyy"
End Sub