Results for "new workbook existing one data"

I think I get what you're aiming for with the macro. So, when you open the file and select a cell like A7, you want the macro to grab certain cells from that row (like A7, C7, E7, G7) and paste them into a new workbook, right?

Hello and welcome to the forum

Try with the code herein

Sub Macro1()
Dim newwb As Workbook
Dim r As Integer
Dim TS As ListObject

Set TS = ThisWorkbook.ActiveSheet.ListObjects(1)

On Error Resume Next
r = TS.ListRows(ActiveCell).Range.Row - TS.HeaderRowRange.Row
If r = 0 Then MsgBox "No cell selected in table !", vbCritical, "Wrong selection": Exit Sub 'if selected cell in not in table range
On Error GoTo 0

Set newwb = Workbooks.Add
With ActiveWorkbook.ActiveSheet
    .Range("A1") = TS.DataBodyRange(r, 1).Value
    .Range("A2") = TS.DataBodyRange(r, 3).Value
    .Range("A3") = TS.DataBodyRange(r, 5).Value
    .Range("A4") = TS.DataBodyRange(r, 7).Value
End With
End Sub

rem : Please think to update the reference of your excel version in your account as "English" does not help. Use excel 2008, 2016,;... or if you are a MacUser --> MAC2011, MAC20xx

Sincerely

I would like the Macro to do the following:

When I open the file and select, for example, A7

Then I run the macro

In the macro, I have a setting that if I first selected a cell from row 7, then the macro places the 1st, 3rd, 5th and 7th cells from row 7 in a new workbook in the following way

1. in A1

3. in A2

5. in A3

7. in A4

Also, I would like to choose the save location

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.