Calculating and copying data into new sheet
 

Hello everyone. I wonder if someone can help.

I have a list of data imported into an Excel spreadsheet into a sheet
called "import".
Everytime the data is imported, the length could be different, but the
range will always be A: to F (starting in row 1 with headers).

Column A - unique identifier (text string)
Column B - first date
Column C - second date
Column D - difference beween C and B
Column E - city (text string).

What I would like to create (in VBA) is a macro to take the data in
sheet "Import"
and copy it into a new sheet and create a new line for each new unique
identifer with multiple entries depending upon the difference in column
D.

For example. a line with the following information in "import"
Column A: A12345
Column B: 21/12/2003
Column C: 24/12/2003
ColumN D: 3
Column E: New York.

Would produce the following output:
A12345 21/12/2003 3 New York
A12345 22/12/2003 3 New York
A12345 23/12/2003 3 New York
A12345 24/12/2003 3 New York

Whereas, the same information but between 21/12/2003 and 22/12/2003
would show:

A12345 21/12/2003 1 New York
A12345 22/12/2003 1 New York.

Any and all help gratefully received.
Thank you.
Scott.


--
scottwilsonx
------------------------------------------------------------------------
scottwilsonx's Profile: http://www.excelforum.com/member.php...o&userid=11128
View this thread: http://www.excelforum.com/showthread...hreadid=263823

Calculating and copying data into new sheet
 

Scott,

Run the macro below while your "import" sheet is active.

I wasn't sure what you wanted to do with column F, so I ignored it.....

HTH,
Bernie
MS Excel MVP

Sub TryNow()
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))
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
Next myCell

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



"scottwilsonx" wrote in message
...

Hello everyone. I wonder if someone can help.

I have a list of data imported into an Excel spreadsheet into a sheet
called "import".
Everytime the data is imported, the length could be different, but the
range will always be A: to F (starting in row 1 with headers).

Column A - unique identifier (text string)
Column B - first date
Column C - second date
Column D - difference beween C and B
Column E - city (text string).

What I would like to create (in VBA) is a macro to take the data in
sheet "Import"
and copy it into a new sheet and create a new line for each new unique
identifer with multiple entries depending upon the difference in column
D.

For example. a line with the following information in "import"
Column A: A12345
Column B: 21/12/2003
Column C: 24/12/2003
ColumN D: 3
Column E: New York.

Would produce the following output:
A12345 21/12/2003 3 New York
A12345 22/12/2003 3 New York
A12345 23/12/2003 3 New York
A12345 24/12/2003 3 New York

Whereas, the same information but between 21/12/2003 and 22/12/2003
would show:

A12345 21/12/2003 1 New York
A12345 22/12/2003 1 New York.

Any and all help gratefully received.
Thank you.
Scott.


--
scottwilsonx
------------------------------------------------------------------------
scottwilsonx's Profile:
http://www.excelforum.com/member.php...o&userid=11128
View this thread: http://www.excelforum.com/showthread...hreadid=263823