VBA - Update button - Select xls file to update from

Hi, I've created a new form for our engineer to enter their project information on and requirements and I'd like to copy certain cells from each new form into an overall form to help track the projects status. I created the button and tried to find some code but haven't come across any that matches what I need. So far my button will let me select the individual form I'd like to update from but that's about it. Then it copies it's location into my selected cell. As I go along I'd like it to update the next free row so that it doesn't the previous entry. Is there a way to do this through VBA? I wasn't sure if there was a way to use the file selected in the 'varFile' below and then also tell it to copy for example cell B11 on the engineer form to cell 'next available row, column 3' etc. Any help or guidance would be great! Thanks.

Private Sub CommandButton1_Click()

Dim varFile As Variant

varFile = Application.GetOpenFilename("Excel files (*.xls), *.xls", , "Select a file", , False)
If varFile = False Then
Exit Sub
Else
ActiveCell.Value2 = varFile
End If

End Sub

To find the next (first) empty row in column C you could use the offset command where the first number is the row offset and the second is the column offset.

Please Login or Register  to view this content.

to paste your value just add PasteSpecial ....

Please Login or Register  to view this content.

Alf

Thanks Alf, that will defentiley come in handy and was what I was hoping for so it copies the information to the next availble row
With the code above that I copied in is there a way that it can read the 'varFile' after I've selected the excel sheet I want to copy from and use it as a reference to that document so it can copy certain cells to my new excel sheet? When I click the button to run it after I select a file it copies the location (eg C:\Documents\randomsheet.xls) into the cell that I have selected.
Thanks again for the help

is there a way that it can read the 'varFile' after I've selected the excel sheet I want to copy from

Not sure I understand you properly. Do you wish to make the 'varFile' the active file, select a specific sheet in the 'varFile' and copy some data from this to your 'Master file' and then close the 'varFile'?

If the 'varFile' sheet name is alvays the same as well as the cell address you wish to copy from this could of cource be included in a macro.

Alf

Master file.pngRequest Form.png

I've attached a couple of photos that will hopefully help. From the 'Job Request Form' which is a completely different excel file I'd like to copy the same cells for example B3, B11 and I12 to the 'Master File' excel file but copy in 'next free row, column B, C and D'. The 'Update' button on my 'Master File' will let me go straight to the 'Job Request Form' that I want to copy the details over from and it'll copy it's location as text into the selected cell. The 'Job Request Form' isn't always in the same folders as it will be with that particular project and it's file name will change as we name it in relation to the project it has been created for.
Hope this helps, I'm not the greatest at explaining things.
Thanks

Ok let me test this idea. You have a folder with a number of files where some of these files have a sheet named "Job Request Form" wher you wish to copy from 3 cells to your master file to the first empty row in column B, C and D.

A macro could be written that loops through all xls files in this specific folder, opens the file and tries to activate sheet "Job Request Form". If this sheet not excists the file is closed and next file is opened and checked. If sheet "Job Request Form" excists. If so value in cell B3 is copied to master column B and first empty row, then cell B11 is copied to column C first empty row and finally cell I12
is copied to first empty row in column D. Then the xls file is closed, next file is opened .............

If on the other hand you need to pick a single xls file and copy 3 values from this specific file to the master you could then try a macro like this.

Please Login or Register  to view this content.

This will open the file you pick, read a value from the active sheet on that file and copy it to the master file (ThisWorkbook) and repeat this two times more and then close the xls file you selected. If the value you wish to copy is on a specific sheet you can activate this after you have opened the varFile i.e.

Please Login or Register  to view this content.

Alf

Hi Alf,
I can see what you're doing with the code and that's exactly what I'm after. I'm not that great at writing code but I am better at trying to understand what it's trying to achieve. The only problem I'm having is that the code isn't copying the cells from the file I select. When I click the button it lets me choose the file I want, opens and closes it in a flash but then it seems to be copying the cells from the master file instead of the chosen file. I commented out 'Application.CutCopyMode = False to see what was happening and it's copying the chosen cells from the master file back into itself. Any idea what the issue might be?
Thanks again Alf

Please Login or Register  to view this content.

Hi Alf,
After some researching and a bunch of trial and errors it looks like I finally have the final set up that works all I need to do is add a few more copy and paste cell commands and it's done. Thanks so much for your help, I couldn't have got this up and running without your help.

The final code is below:

Please Login or Register  to view this content.

I had to change the 'xlPasteAll' to 'xlPasteValues' as it was copying over the conditional formatting I had in place and I only wanted the value of the cell. I also read someone comment that adding in the 'Application.ScreenUpdating' can help to speed the process up.

'Application.ScreenUpdating' can help to speed the process up.

Yes that is true because updating the screen every time the macro does something takes time and as one only need to see "the final result" you could place the line

Please Login or Register  to view this content.

at the start of the macro and

Please Login or Register  to view this content.

at the end.

Thanks for feedback.

Alf