#1
|
|||
|
|||
Copy and Paste from File to File but File Names always change
First I'll let you know what I have and then let you know what I'm trying to accomplish.
I have a file - products.xlsm with a copy row macro that will copy from "XYZ worksheet" and paste to "Quote worksheet" This is all working fine and no issues. What I'm trying to accomplish is to be able to use the copy past macro to paste to a "quote worksheet" that exists in different files. These different files would have a different name all the time, never to repeat. Also there could be many of these files open all at the same time. Is there a way in my products.xlsm workbook to set up my copy/paste row macro to default to whatever file I choose? for example...I have the following files open. products.xlsm jon.xlsm jane.xlsm bob.xlsm All of these files have the same worksheets in them titled "quote" I want to be able to copy anything in the products.xlsm file and paste to any of the files of my choosing onto the "quote" worksheet. This copy and paste function would happen several hundred times so it would be nice to have something where I don't have to select the file every single time I copy/paste. Be able to set the destination of the copy/paste macro and it be this same destination until I change it. Possibly a cell A1 in the products.xlsm file where you type the file name of where you want to paste to and the macro reads this file name to complete the copy/paste macro function??? Really have no clue how to accomplish this. Any help is appreciated. Thank you! |
#2
|
||||
|
||||
If I understand you correctly, your program will write some data to a workbook; you just want to instruct your program which workbook to write it to. You'll be running the program many times, and the target workbook will vary from run to run, so being able to put the filename in a cell would be convenient for you.
If that's what you want to do, sure, it'll work. I do it all the time; my current client has me writing many tools, one after the other, and I finally got the idea of having a Settings worksheet in each .xlsm. Each row on the Settings worksheet contains a setting name in col 1 and a value in col 2. I wrote a VBA class that reads the Settings worksheet at the beginning of each run, and puts all the settings in a public collection where the various programs can fetch the values they need. For example, one common value is Path; the value "Path" is on some row in col 1 of Settings worksheet, and next to it is the Path of the desired output. (A blank setting just means "put the output in the same folder as ThisWorkbook".) Another is UserSpecs, where the user of the program can type in a list of department codes or LAN IDs, which tells the program which users to list in the report. And so on; you can make up almost anything. It's just like having a Constant somewhere in your program, except by putting it in the Settings worksheet you allow the program's user to change a value before each run. In this case you need a setting for the output filename, that's all. You don't have to write a Settings class if you don't want to, but if you keep on with this I'm betting you will eventually. That makes it much easier to create new settings as the need arises. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copy and paste file but folder doesn't update | Haha88 | Windows | 2 | 09-24-2015 09:54 AM |
Keeping track of the original file when you copy/paste | sidbisk | Excel | 2 | 09-01-2015 02:11 PM |
Copy/Paste from Paint to Word - There is a problem saving the file | one2three | Word | 1 | 10-26-2011 07:35 AM |
How to copy automatically data from Excel file to Word file? | fuchsd | Word | 6 | 10-25-2011 05:52 AM |
Can no longer copy and paste an email file into a task | kc300c | Outlook | 0 | 07-16-2010 04:34 PM |