Auto picking of value from multiple sheets.

[hiteshkkk]

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 |
+-------------------------------------------------------------------+

 

[Toppers]

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