VBA Generic Workbook Name?

[crhoads]

I am running a macro to copy data from one workbook to another. It works exactly the way I want it to, but I need one of the workbooks names to be generic and not fixed. Here is the code that I have:

Please Login or Register  to view this content.

The workbook that will change is "Last Name, First Name.xlsx." I either need to be able to change the name to something generic, or have the code prompt me to open a file at the beginning and then insert the name of that file...whichever would work. There would always be two workbooks open (the "Classroom Progress Report" and the workbook who's name will change), so I'm not sure if that helps. They will also always be opened in the same order ("Classroom" as the first window and the other as the second). I am pretty new to this and I have been trying to figure this out for awhile now...ack! Thanks in advance for any help!

[mrice]

Suggest that you have a look at the GetOpenFilename method of the Application object. This will give a standard file selection dialog box.

Code snipets might help

Please Login or Register  to view this content.

[TMS]

You can use something like this:

Please Login or Register  to view this content.

Regards, TMS

[crhoads]

Would I just put this at the top of my code? I'm sorry, I am super new at this, haha!

[TMS]

Yes, you would put it at the top of the code and then, instead of referring to the hard coded workbook name, you'd use the relevant variable. You don't need the Msgbox; they're just there to demonstrate the approach.

You could even combine it wit Martin's suggestion if you want the user to determine which file(s) to use.

Also, avoid using "Selection. ... .Select". For example:

Please Login or Register  to view this content.

should be:

Please Login or Register  to view this content.

Regards, TMS

[crhoads]

Is there a way to prompt the user to open the file and then enter that file name in where it needs to go?

I am so hopeless at this...I put it at the top and changed everything but I still can't get it to work.

[TMS]

Can I suggest that you upload a sample workbook, ideally with a sample of the workbook you want to use with it, with the code as you have it right now.

You don't want them to open the file, you want to ask them to navigate to the file ... give YOU the file name ... so that you can open it under program control.


Regards, TMS

[crhoads]

I am attaching the sample workbooks. The sample classroom workbook is where the data would be copied from, and the sample individual workbook would be the variable workbook where the data is pasted...the name of the workbook changes with each child.

Thank you so much for the help, I have been trying to figure this out and don't know where to go from here. I am not well-versed in VBA, I generally just record macros and do simple edits to the code as needed.

[TMS]

And where does the code "live"?

[crhoads]

I have it saved in my Personal workbook so it carries between workbooks. Is that where it should be at? I just left it back to where it was originally (posted in my first post) because I didn't want to mess it up further, haha!

[TMS]

Oh, and forget the code: please describe the process ... What you are trying to achieve, which rows you want to copy from where to where and how you choose them.

Regards

[crhoads]

The data needs copied from a particular row (Child's Name) on the sample classroom report onto the appropriate corresponding question in columns I and N on the individual workbook. Ex: for Child 1, the data corresponding to 1a (column I) will need pasted with the corresponding question on that child's workbook (manages feeling, row 3, column I). The objectives just go in order...1a is manages feelings, 1b is follows limits and expectations, etc. It gets complicated because I do this four times a year, so the first pull down (which is already done) gets pasted into column 1 on the child progress report, the second pull down gets pasted into column I, etc. I am just trying to figure out some way of getting it to paste since the workbook for each child is named differently. Then each pull down I can just change the column letter that it gets pasted into in the code.