Automatically fill an Excel table via another one

good morning all !

Could you please give me some help ! I attached basically what I woould like to do on excel :

step 1 : from 1No file called "data file.xls" , fill up manually information (I know how to do)

step : 2 : export the information from "data file.xls" to a new file called "supervisor.xls" (I know how to do)

step 3 : I would like that the "supersivor.xls" fill up automaticlly the information coming from "data file.xls" once the variable Note or Date changed ( I do not know how to do !)

I do not know from where to start basically ...

Do you have any tips ?

Thank you very much !

Hi benbenben89,

How do you do the step 2 ? With VBA ?

And basically, it's easier for us to help you if you attach an excel file rather a pdf. Tough your pdf is very clear.

And you say that you want the information in supervisor.xls to be filled automatically once the variable Note OR Date changed. Are you sure ? So with the datas of your pdf, if I change the 3 in the Note column, it automatically fills the supervisor file ?

Hi !

To do the step 2 , basically I am using the "connection fonction " located in the excel "data" menu . But may be the best way is to do everything in VBA.

My variable are

I have not create est the supervisor .xls file , as I do not know how to fill it automaticlly

"So with the datas of your pdf, if I change the 3 in the Note column, it automatically fills the supervisor file ?" : yes exacly

I am chasing the program

If you have any idea , please let me know

Do you have excel 2008 for Mac ? If yes, using VBA is not possible.

VBA with excel 2008 ; why that is not possible ?

Simply because Microsoft decided not to implement VBA in excel 2008.

sorry excel 2007 ! I have the VBA editor

Ok.

On the sheet named Sheet1 there are 8 columns of datas. Which one do you want to put in the supervisor file ?

And there is always 1 row of datas (on row 5), am I alright ?

the data located in "sheet 1" take the information of the "table sheet" .Indeed , if you change in the "table sheet " the quantity of Status A , Status B etc etc , the quantity is sent to the "sheet1" . That is the first point .

Because I will have in total 5No different data file , I need what I called a supervisor file in order to summarise and get an history about all the information on 1No sheet.

Once I will update the A2 OR B2 located in the " table sheet" , I would like that in the "supervisor" file , 1No row is create and fill up automaticlly with the information located "sheet 1 table "

I have a question : what does "5No", "1No" mean ?

5No means 5 quantity , sorry for the syntax

Ok. Here are the steps to successfully import datas in the supervisor file.

1- Create a file named supervisor.xls in the same directory as the datafile

2- In the data file, right-click on the sheet named table and click on "Visualize the code" or something like that

3- Paste the following code :

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim chemin As String
    Dim NoteAndDate, Sttatus
    Dim SupFile As Workbook
    Dim derlign As Long

    If Not Application.Intersect(Target, [a2:b2]) Is Nothing And Target.Count = 1 Then
        Application.ScreenUpdating = False
        chemin = ThisWorkbook.Path & "\"
        NoteAndDate = [a2:b2]
        Sttatus = Application.Transpose([b41:b43])
        Workbooks.Open (chemin & "supervisor.xls") 'open the supervisor file if it is in the same directory as the datafile
        Set SupFile = ActiveWorkbook
        With SupFile.Sheets(1)
            derlign = .Range("a" & .Rows.Count).End(xlUp).Row + 1
            .Range("a" & derlign).Resize(, 2) = NoteAndDate
            .Range("c" & derlign).Resize(, 3) = Sttatus
        End With
        SupFile.Close True 'save and close the supervisor file
    End If
End Sub

4- Now change the value in A2 or B2 of the sheet table

5- Datas are imported in the supervisor file which is saved and closed.

that is exacly what i wanted !! thank you so much !

Now i need to found a way to automaticlly trace a graph with the evolution of the status A , status B and not commented (vertical axe) and date (horizontal graph)

i need as well now to ajust the code in order to integrate it into 5 different data file ( data file 1 , data file 2 , data file 3 , data file 4 ,data file 5) which will send into the supervisor file into 5 differents place .

Thank you very much

benbenben89 a écrit :

Now i need to found a way to automaticlly trace a graph with the evolution of the status A , status B and not commented (vertical axe) and date (horizontal graph)

benbenben89 a écrit :

i need as well now to ajust the code in order to integrate it into 5 different data file

    If Not Application.Intersect(Target, [a2:b2]) Is Nothing And Target.Count = 1 Then
.
.
.

        NoteAndDate = [a2:b2]
        Sttatus = Application.Transpose([b41:b43])

benbenben89 a écrit :

which will send into the supervisor file into 5 differents place .

Basically the 5No different files will be on a network at 5No different place (folder) .

At least the number of file will increase as in reallity 1No data file = 1No project .

What I would like is to have 1No sheet located in the supervisor file by project . If I do a new project , that will create the table in a new supervisor sheet ?

When I update the data file , I can put a message box saying "supervisor file upfdated " ?

Thank you !

Where should I send you a postcard ?

Can you create 2 datafiles with the real name and attach them here ?

benbenben89 a écrit :

At least the number of file will increase as in reallity 1No data file = 1No project

benbenben89 a écrit :

What I would like is to have 1No sheet located in the supervisor file by project

benbenben89 a écrit :

If I do a new project , that will create the table in a new supervisor sheet

benbenben89 a écrit :

When I update the data file , I can put a message box saying "supervisor file upfdated " ?

benbenben89 a écrit :

Where should I send you a postcard ?