Macro copying data from unopened worksheet to open worksheet

**chriswiec** · 12-31-2012, 12:04 AM

Hey guys,

Im trying to find code that allows me to run a macro from the sheet im in and once i run it I want it to ask me which file I want to copy data from I then choose that file, once I do that I want it to choose specific preselected cells and copy that data into specific cells in the worksheet I have open. For example I have copper weight column in t38414.093thickpolyimide file and I want that info copied into the copper weight cells in the glassstocalc file. Now there are several layers and for each layer that gets higher there is more data, but each sheet will have its own Macro. Basically I want to copy data from the t38414 file to the glassstopcalc file. I want the macro to ask me which file I want to open then copy the data from there.

**Cofad** · 12-31-2012, 01:21 AM

Something like this might work:

Please Login or Register  to view this content.

**chriswiec** · 12-31-2012, 01:38 AM

Well not exactly Did I mention I know very little about macros. I need it to prompt me for a specific file I want to open, then from there i can program in the ranges i want to copy then paste to I believe. We'll see.

**Cofad** · 12-31-2012, 02:02 AM

Well, what better time than now to try and figure out this macro stuff! It can be tricky, but it is very helpful once you get the basics. So I would suggest creating a userform to prompt for all the data and have a button on there that will run the macro I posted earlier.

The user forms are fairly straight forward to setup. From the visual basic editor go to Insert>>Userform then add the text boxes and buttons. You can right click the button and add the code there.

I've attached an example, but it is not real clean. I did not put in any error checking so it might not work super smoothly. Hopefully you can get it to work and see the idea and perhaps implement it.

**chriswiec** · 12-31-2012, 02:21 PM

I'm confused on user form and how to copy to a different worksheet, also how do i change the cells it is copying into, also is there a way instead of typing the path to file it would have me choose from a file selection which file I want to choose?

**chriswiec** · 01-01-2013, 01:41 AM

Is it possible to copy user form from one file to another guys?

**Cofad** · 01-01-2013, 02:14 PM

Yes.

Open the file with the userform, open the visual basic editor (Alt+F11), find the userform in the object browser, right click and select export, and export to a good location.

Next, open the file you want to copy the userform to, open the visual basic editor, find the workbook in the object browser and expand its file, right click in the files for the workbook, select import file, select the file from the first part.

That should copy the userform over and then you can manually copy paste the vba.

I'm not sure at the moment how to open a file selection box, but it can be done.

Also, if you can tell me which cells are of interest in your workbook, I could probably implement it for you. I'm just now sure what data you need and where it needs to go.