anyone can help me in vb excel programming?

Question

i need to make a report using excel.currently im doing it manually bycopying a certain graph from an excel file to another excel file.but i want to automate the process.how do i copy a certain graph in one excel to a new excel and arrange it accordingly.i have 4 excel file that i need to compile into one excel report.any ideas?

Answer 1

You can do that by using the Macro Recorder.

Go to Tools > Macro > Record New Macro

The recorder will record all of your actions in programming code and you do stuff in Excel. Once you get done with the actions that you want to repeat, then stop the Recorder. Go back to the Macro by selecting:

Tools > Macro > Macros...

Then select the Edit Button for the macro you just created. You may need to edit the Macro a little bit for the name of the workbook if you are going to a different named workbook.

You can then make the control toolbox toolbar visible and add a command button. Then right click the command button and select View Code. Copy the code from the macro to the command button. Then go back and activate the command button by click the button that looks like drafting triangle.

Now when you click the command button it will perform all of those actions. If you go back into draft mode on the command button by clicking the triange again. You can change the PrintObject property to false so that the command button doesn't print out.

Answer 2

Easiest way I can suggest is to open up all the reports and then create your graph. In the series or data range value you can enter =[the Report Name.xls]worksheet name!then the cells ie =[Report1.xls]Sheet1!$A$3:$A$10 or minimize the value then go and highlight the values needed and this will be picked up into this new report.