auto update in next sheet..

[kundanlal]

I have a file with data in sheet1 as follows which is being updated by downloading through WEB every day. The data gets replaced for every date.


Date Name of Fruit Price


10-Oct-10 Banana 12

10-Oct-10 Apple 60

10-Oct-10 Mango 100


I need to create a consolidated data for each day as follows. The data needs to replace in sheet2 as under with a close of xls sheet/automatically without disturbing earlier data columns.


Date ====> 10-Oct-10 11-Oct-10 12-0ct-2010 13-Oct-10

Name of Fruit


Banana 12 12 13 14

Apple 60 61 62 63

Mango 100 101 102 104


can anybody help?

 

[Hui]

Kundanlal


The following basic code will do what you want

Save it to the Workbook Module in VBA


It will transfer the data every time you save the file

If there is nothing in Sheet1!A2 it won't insert a blank column on sheet 2

[pre]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("Sheet1").Range("A2").Text <> "" Then 'If nothing in Sheet1 A2 skip

Sheets("Sheet2").Select 'Insert extra column on Sheet 2
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Sheets("Sheet1").Select 'back to sheet 1 and copy data
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select 'Back to sheet 2 and paste it
Range("B2").Select
ActiveSheet.Paste

[B1] = Sheets("Sheet1").Cells(2, 1).Value 'Transfer date

Sheets("Sheet1").Select 'Back to sheet 1 and clear old data area
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents

Range("A2").Select
End If

End Sub

[/pre]

 

[kundanlal]

Sir,


I am very new to VBA. Can u pl. guide me..This may be very funny, but I need to do this.

 

[Hui]

Copy the code

In Excel access VBA by Alt F11

On the left side you will see a tree of your workbook with possibly 3 sheets (Sheet1, Sheet2 & Sheet3) and a Workbook Icon

Double click on the Workbook icon and then paste the code in the right hand pane


Note the first time you save your file you have to save it as a Macro Enabled Workbook


I have put your example data in here:

http://rapidshare.com/files/426887466/Save_Fruit.xlsm

 

[kundanlal]

Dear Sir,


I have tried this but it is not working. I may be somewhere wrong. I did the exactly same procedure as u guided me. The file you have kept in the link is having sheet1 marked with Blue Colur and the sheet2, it is all mixed up..Pl. help me to sort this out.

 

[Hui]

Can you post the file somewhere ?

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[kundanlal]

I have uploaded the file here http://www.speedyshare.com/files/24936274/kbmanwatkar_fruitbook.xlsx


pl. see..Thanks...

 

[Hui]

Have a look at

http://rapidshare.com/files/427908827/kbmanwatkar_fruitbook_Hui.xlsm


Some rules


The fruit must be in the same position/order every day on Sheet1

ie: Starting in A3

[pre]

14-Oct	Banana	10
14-Oct	Apple	15
14-Oct	Mango	12[/pre]
You can add fruit but don't upset the fruits order!


Change the values and save the workbook

try a few times and check Sheet 2 each time


When ready go to the line in VBA in MyWorkbook

[code]'    Selection.ClearContents 'UNCOMMENT THIS LINE (REMOVE FIRST '  ) WHEN READY TO USE PROPERLY

and remove the leading '


The line will now say

Selection.ClearContents 'UNCOMMENT THIS LINE (REMOVE FIRST ' ) WHEN READY TO USE PROPERLY[/code]


Save again and it will now shift the data every time you save the file

If there is no data it won't insert a blank column

 

[kundanlal]

thanks..