Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help Taking alot data from one sheet (if not blank) and copying toa list on another sheet. | Excel Worksheet Functions | |||
copying only selected data from Sheet 1 to Sheet 2 | Excel Worksheet Functions | |||
Copying the repeated data of the previous sheet to the next sheet | Excel Discussion (Misc queries) | |||
Copying Data from one sheet to another sheet on a specific day | Excel Worksheet Functions | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions |