Results for "automatically fill table via another one"

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 ?

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 ?

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 .

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

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.

5No means 5 quantity , sorry for the syntax

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

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 "

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 ?

sorry excel 2007 ! I have the VBA editor

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

VBA with excel 2008 ; why that is not possible ?

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

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

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 ?

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, I totally agree with Saboh. But if you still want to use VBA, here is some starting points :

Last row based on column A :

LR = Sheets("SheetName").Range("A" & Rows.Count).End(xlUp).Row

Writing formula with VBA :

Sheets("SheetName").Range("A1").Formula = "=SUM(B1:B" & LR & ")"

You can also use generic formulas like =SUM(B:B), but those ones use quite more ressources.

Hello Noura and welcome to the forum,

If I may, you're not following the right approach. There are native Excel tools that allow you to do this in a simple and intuitive way, without using VBA (old, security concerns, subject to bugs…).

Based on your description, it sounds like you're looking for tables. Here's a great tutorial that gives a comprehensive overview of their features: Excel table: comprehensive tutorial with examples.

For your information, in addition to automatically extending your formulas, this will also apply to pivot tables.

Hi everyone,

I'm an investment analyst and new to using VBA. I have a spreadsheet where I add new data every month (one new row each time). I want to use VBA to:

• Find the last row with data
• Automatically extend formulas to include the new row
• Update named ranges or charts so they stay current

What is the best way to do this in VBA?
Any simple examples or advice would be really helpful.

Thank you!

Best regards,

Noura

thanks again