Help the research with a Macro!

[stonemeister]

Hi Excellences!

I have here a quite complicated brain teaser.

I have created an .ods file (excel wasn't able to menage this amount of formulas, and I called this file "Dynamic") that allows me to make calculations of a huge amount of data, that I have collected in different sheets of different files.

Here's what I need to do for my research in the genetics field:

1) To copy all the values from A2 till V91, from the "DataSheet1", of the "DataFile1", into the "DynamicSheet" of "Dynamic" in position B4.

2) To copy the name of this "DataSheet1" into the cell AB17 of "Dynamic".

3) "Dynamic" will make calculus with this data and the results will appear in the sheet "summary" of the same file. I need to copy this results from the sheet "summary" cells C2:G23 and paste them in another file that i will call "MASTER".

4) I have to do this with all the sheets of all the files.

My english is not perfect and the work is complicated, i hope it's understendable and i really hope there is someone so good with VBA and so gentle to help a researcher doing his (without pay) job.

Thank you ahed of time!! i'm here for any clarification. Thaks again!

 

[yky]

Try this:

Sub test()

Dim wk1 As Workbook
Dim wk2 As Workbook
Dim wk3 As Workbook


Set wk1 = Workbooks.Open("d:\mypath1\Dynamic.ods")
Set wk2 = Workbooks.Open("d:\mypath2\DataFile1.xlsx")
Set wk3 = Workbooks.Open("d:\mypath3\MASTER.xlsx")


'To copy all the values from A2 till V91, from the "DataSheet1", of the "DataFile1", into the "DynamicSheet" of "Dynamic" in position B4


wk2.Sheets("DateSheet1").Range("A2:V91").Copy Destination:=wk1.Sheets("DynamicSheet").Range("B4")


'To copy the name of this "DataSheet1" into the cell AB17 of "Dynamic".


wk1.Sheets(1).Range("AB17").Value = wk2.Sheets("DataSheet1").Name


'I need to copy this results from the sheet "summary" cells C2:G23 and paste them in another file that i will call "MASTER".


wk1.Sheets("Summary").Range("C2:G23").Copy Destination:=wk3.Sheets(1).Range("A1")


End Sub

 

[stonemeister]

Thank you for helping yky

Your code makes exactely what it has to do but i realized now that the code has to be in openoffice basic because once excel open the .odt file in which are all the formulas automatically starts giving error messages.

My problem is I know few about VBA and I know nothing about the openoffice version.

I'm new here, is this the right place where to talk about this kind of stuff? Thanx yky, thx everyone!