VBA CODE. Button to browse and copy files into a workbook.

[mergim]

Hello there,

I am trying to make a button that opens up the browser where I then can load an excel file, which then copies the data from the loaded file. The data I want to copy is marked in yellow on picture one. Here the range is A5:F16, however that sometimes changes, so I need some sort of a dynamic range.copy. Maybe you could say something like copy range from where a cell value in column a is = 1.currentregion, in this case cell A5.
The number of rows can vary, so I need some sort of code that copies the last line, in this case to F16. I also want the button to be able to load files endless times. Lets say I have loaded one file, which goes from A5:F16. The second time I load a file, it needs to start from the next empty row which, in this case, would be row A17.
The last thing that I hope is possible, is to add the text after "Design id.:", which in this case is "12345-12345-12345" (see A1) to the amount of rows there are, in this case from A5 to F17, so 12 rows on the left of "Process no" in column A.

I hope this makes sense and is possible.

Thanks in advance,

 

[mergim]

Hello Rob,

This works perfect! Thank you so much, it is deeply appreciated.
I will try and play with it, and hopefully I do not come across any errors.
Again thank you so much.
Have a lovely weekend!

 

[RobP]

Hi

Sorry a Teams call won’t be possible as I’m on holiday this week . Better you try to explain the issue to see if others can help, as unfortunately I cannot for next 2 weeks

Cheers
Rob

 

[mergim]

Hi

Sorry a Teams call won’t be possible as I’m on holiday this week . Better you try to explain the issue to see if others can help, as unfortunately I cannot for next 2 weeks

Cheers
Rob

Hello Rob,

I managed to figure it out

 

[mergim]

Hello Rob,

I managed to figure it out

Hello Rob.

I hope you are all well and enjoying your vacation.

I am trying to add this function, where when I load the data, the pipe design value is also copied to another worksheet called "Pipe overview". The pipe design is, as you know, in A1.
Right now, the value is only copied into the active sheet, but I also want it in this "Pipe overview" worksheet. And the design should only be added once in the last empty row.

Below is the code for copying into activesheet.

 ActiveSheet.Range(Cells(intlastrow + 1, 1), Cells(intlastrow + newdatarows, 1)) = design_id
    
    
    'split the RM  Revision number if there is 9 chars or more ie. a rev. no on the end of it
    For x = intlastrow + 1 To intlastrow + newdatarows
        If Len(CStr(Cells(x, 5))) > 8 Then
            Cells(x, 6) = Mid(Cells(x, 5), 10)
            Cells(x, 5) = Left(Cells(x, 5), 8)
        End If
    Next x
    
    Set master_array = Nothing  'clean memory
    
End If

 

[mergim]

Hello Rob.

I hope you are all well and enjoying your vacation.

I am trying to add this function, where when I load the data, the pipe design value is also copied to another worksheet called "Pipe overview". The pipe design is, as you know, in A1.
Right now, the value is only copied into the active sheet, but I also want it in this "Pipe overview" worksheet. And the design should only be added once in the last empty row.

Below is the code for copying into activesheet.

 ActiveSheet.Range(Cells(intlastrow + 1, 1), Cells(intlastrow + newdatarows, 1)) = design_id
   
   
    'split the RM  Revision number if there is 9 chars or more ie. a rev. no on the end of it
    For x = intlastrow + 1 To intlastrow + newdatarows
        If Len(CStr(Cells(x, 5))) > 8 Then
            Cells(x, 6) = Mid(Cells(x, 5), 10)
            Cells(x, 5) = Left(Cells(x, 5), 8)
        End If
    Next x
   
    Set master_array = Nothing  'clean memory
   
End If

In should add it into B2 in "Project overview´" sheet

 

[RobP]

Hi Mergim,

this subroutine should do what you need, based on the info you provided. You can see your filename, and sheetnames coded below, so you will need to correct if they are different. It also assumes this file is in the same subdirectory as your main, else you need to add a full pathname.

Sub project_overview(design_id)

    Dim projectlastrow As Long
    
    Workbooks.Open ("Pipe overview.xlsx")
    bookname = ActiveWorkbook.Name
    
       projectlastrow = ActiveWorkbook.Worksheets("Project overview").Cells(Rows.Count, 2).End(xlUp).Row ' find the last row of data in the datafile
       ActiveWorkbook.Worksheets("Project overview").Range(Cells(projectlastrow + 1, 2), Cells(projectlastrow + 1, 2)) = design_id 'store design_id in Col. 2 ("B") starting row 2
    
    Workbooks(bookname).Save ' save the file with new data just added
    Workbooks(bookname).Close   'close the data file without any warning msgs..

End Sub

To call the subroutine from the main code, just insert the following line below where the previous file was closed (I added those lines below to highlight where to put it). The code should look like this

 ' close the datafile now we've used its data
    Workbooks(bookname).Saved = True 'make it think its saved already to avoid warning popup.
    Workbooks(bookname).Close   'close the data file without any warning msgs..
    
    '******** subroutine to store design_id number into seperate project overview file *********************
    project_overview (design_id)
    
    '******** Store Data to main file below *********************************
    intlastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row 'find the last row of data in the main file

 

[mergim]

Hi Mergim,

this subroutine should do what you need, based on the info you provided. You can see your filename, and sheetnames coded below, so you will need to correct if they are different. It also assumes this file is in the same subdirectory as your main, else you need to add a full pathname.

Sub project_overview(design_id)

    Dim projectlastrow As Long
   
    Workbooks.Open ("Pipe overview.xlsx")
    bookname = ActiveWorkbook.Name
   
       projectlastrow = ActiveWorkbook.Worksheets("Project overview").Cells(Rows.Count, 2).End(xlUp).Row ' find the last row of data in the datafile
       ActiveWorkbook.Worksheets("Project overview").Range(Cells(projectlastrow + 1, 2), Cells(projectlastrow + 1, 2)) = design_id 'store design_id in Col. 2 ("B") starting row 2
   
    Workbooks(bookname).Save ' save the file with new data just added
    Workbooks(bookname).Close   'close the data file without any warning msgs..

End Sub

To call the subroutine from the main code, just insert the following line below where the previous file was closed (I added those lines below to highlight where to put it). The code should look like this

 ' close the datafile now we've used its data
    Workbooks(bookname).Saved = True 'make it think its saved already to avoid warning popup.
    Workbooks(bookname).Close   'close the data file without any warning msgs..
   
    '******** subroutine to store design_id number into seperate project overview file *********************
    project_overview (design_id)
   
    '******** Store Data to main file below *********************************
    intlastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row 'find the last row of data in the main file

Thanks Rob, once again!

 

[mergim]

Hello Rob

Is it possible to tell the code to extract/obtain the text after a certain text. The code I have right now is telling to obtain the last 21 letters (see code below), but this is not dynamic, sometimes it can be 15 or 18. Then it copies some of the "Design id.:" text, which I don't want. Is it possible to tell the code to copy the text after "Design id.:", so it becomes dynamic ?

For example in the current code we have.

design_id = Right(ActiveSheet.Cells(1, 1).Text, 21) 'obtain the Design_id number from "A1"

Thanks a lot!

 

[RobP]

Hi Mergim,

does this fix your problem ?

design_id = Mid(ActiveSheet.Cells(1, 1).Text, 13)

essentially it grabs everything AFTER position 12 (ie. starting 13)

thanks
Rob

 

[mergim]

Hi Mergim,

does this fix your problem ?

design_id = Mid(ActiveSheet.Cells(1, 1).Text, 13)

essentially it grabs everything AFTER position 12 (ie. starting 13)

thanks
Rob

Thanks Rob, it works perfectly