Results for "automatically update formulas ranges new rows added each"

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

Aha merci !! Mais bien sûr comment ai-je pu ne pas y penser plus tôt !!

Bonne nouvelle année à vous Curulis !

Hello Jeanne,

When you want to delete rows in the same time you're looking after items, you have to write your FOR-NEXT code from bottom to the top of the rows!!
Try this!

Have a happy new year!

Hi, I hope you are well,

I am encountering a problem with a function/code that I built in order to:

Go through the rows of a sheet one after the other and :

-Copy the rows in which the first cell contains a string,

-Paste the rows in a second sheet

-Delete said rows on the parent file

The thing is, this code is partly working, some of the rows which contain the string are indeed copied and pasted but some arent ! And I am not understanding why.

The rows that are sucessfully copied also are not the same ones when I rearrange their place in the sheet

See below my code

My code:

Sub ctrlcctrlv()

Index1 = 1 'index for selecting parent sheet where to find rows
Index2 = 2 'index for sheet where to paste the rows

IndexL2 = 1 'index for pasting rows one after the other on sheet 2

anomalie = "example" 'the string I'm looking for

For Each rw In Worksheets(Index1).Rows
libellé = rw.Cells(1, 1) 'the string in first cell of my row for which I want to check if it contains the string I'm looking for
If InStr(1, libellé, anomalie) <> 0 Then
rw.Copy Worksheets(Index2).Rows(IndexL2)
rw.Delete
IndexL2 = IndexL2 + 1
End If
Next

End Sub

edit moderation : code put between code tags, using "</>" button on the menu bar. Please make sure to use it for future messages.

Thank you very much for anyone who will take the time to answer.

I think i might change methods all over ... :[ maybe look for my string in the whole column directly and store the indexes that come up :(

Sincerely,

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