Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto picking of value from multiple sheets.
hello all.. i want to prepare a macro, thru which i can create a new sheet out of values from diffrent sheets. i have online commodities trading system and we get a daily closing rate file from our exchange. this files are in MS Excel CSV format and are genrated daily. these files contain data relevent to commodities like Closing, Opening, High, Low, etc. now, what i want to do is, prepare an excel sheet in which i will get commodity specific data. the details are as below. File received from Exchange : BCyyyymmdd.csv (received daily in the morning) Columns to be imported from this file in the new sheet: A,F,G,N Column A refers to date. it is same throughout all the rows in that column in a particular file. Column F refers to name of commodity. every commodity has 2-3 contracts with diff expiry date (column G) and its relevent closing price is reflected in Column N. i want import this files daily n prepare a table which can give me comparision of closing rates(N) of diff contracts of same commodity(F) on the basis of expiry date(G). Destination of these fields in new sheet: Commodity name should replace worksheet name. A new date should be added every time a new file is imported and closing rates on that dates should be inserted in front of that date. i have attached 2 screen shots of the master file and out put file. pls help me out in this.. tks n rgds. hitesh +-------------------------------------------------------------------+ |Filename: Output.jpg | |Download: http://www.excelforum.com/attachment.php?postid=3655 | +-------------------------------------------------------------------+ -- hiteshkkk ------------------------------------------------------------------------ hiteshkkk's Profile: http://www.excelforum.com/member.php...o&userid=25705 View this thread: http://www.excelforum.com/showthread...hreadid=391210 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto picking of value from multiple sheets.
Hi,
A starter. Add code to the Output workbook . It assumes the CSV file is open. Option Explicit Sub Main() Dim csvDate As String, csvname As String csvDate = Application.InputBox( _ prompt:="Enter date as YYYYMMDD ", Type:=2) ' No validation of date format ... csvname = "BC" & csvDate Call UpdateOutputFiles(csvname) End Sub Sub UpdateOutputFiles(csvfile) Dim lastrow As Long, i As Long, commodity As String Dim outrng As Range Workbooks(csvfile & ".csv").Activate With Worksheets(csvfile) lastrow = Cells(Rows.Count, "A").End(xlUp).Row i = 1 commodity = "" Do While i <= lastrow If Cells(i, 6) = commodity Then outrng = Cells(i, "G") outrng.NumberFormat = "dd-mmm-yy" outrng.Offset(1, 0) = Cells(i, "N") Set outrng = outrng.Offset(0, 1) i = i + 1 Else commodity = Cells(i, "F") ' New commodity .... Call AddWorksheet(commodity) ' Worksheet if needed ... Set outrng = ThisWorkbook.Worksheets(commodity).Range("b2") outrng.Offset(0, -1) = "Date" outrng.Offset(1, -1) = Cells(i, "A") outrng.Offset(1, -1).NumberFormat = "dd-mmm-yy" End If Loop End With End Sub Sub AddWorksheet(strName) Dim sh As Worksheet On Error Resume Next Set sh = ThisWorkbook.Worksheets(strName) On Error GoTo 0 If sh Is Nothing Then Set sh = ThisWorkbook.Worksheets.Add ThisWorkbook.ActiveSheet.Name = strName Else MsgBox strName & " already exists" End If End Sub HTH "hiteshkkk" wrote: hello all.. i want to prepare a macro, thru which i can create a new sheet out of values from diffrent sheets. i have online commodities trading system and we get a daily closing rate file from our exchange. this files are in MS Excel CSV format and are genrated daily. these files contain data relevent to commodities like Closing, Opening, High, Low, etc. now, what i want to do is, prepare an excel sheet in which i will get commodity specific data. the details are as below. File received from Exchange : BCyyyymmdd.csv (received daily in the morning) Columns to be imported from this file in the new sheet: A,F,G,N Column A refers to date. it is same throughout all the rows in that column in a particular file. Column F refers to name of commodity. every commodity has 2-3 contracts with diff expiry date (column G) and its relevent closing price is reflected in Column N. i want import this files daily n prepare a table which can give me comparision of closing rates(N) of diff contracts of same commodity(F) on the basis of expiry date(G). Destination of these fields in new sheet: Commodity name should replace worksheet name. A new date should be added every time a new file is imported and closing rates on that dates should be inserted in front of that date. i have attached 2 screen shots of the master file and out put file. pls help me out in this.. tks n rgds. hitesh +-------------------------------------------------------------------+ |Filename: Output.jpg | |Download: http://www.excelforum.com/attachment.php?postid=3655 | +-------------------------------------------------------------------+ -- hiteshkkk ------------------------------------------------------------------------ hiteshkkk's Profile: http://www.excelforum.com/member.php...o&userid=25705 View this thread: http://www.excelforum.com/showthread...hreadid=391210 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-scrolling multiple sheets | Excel Worksheet Functions | |||
Auto link rows of information from multiple sheets to single sheet | Excel Discussion (Misc queries) | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
Looping between sheets picking up values on the way | Excel Worksheet Functions | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions |