Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-12-2016, 10:30 AM
aaronbauer1980 aaronbauer1980 is offline Copy and Paste from File to File but File Names always change Windows 10 Copy and Paste from File to File but File Names always change Office 2016
Novice
Copy and Paste from File to File but File Names always change
 
Join Date: Apr 2016
Posts: 3
aaronbauer1980 is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 04-15-2016, 05:53 PM
BobBridges's Avatar
BobBridges BobBridges is offline Copy and Paste from File to File but File Names always change Windows 7 64bit Copy and Paste from File to File but File Names always change Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 676
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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.
Reply With Quote
Reply

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
Copy and Paste from File to File but File Names always change Keeping track of the original file when you copy/paste sidbisk Excel 2 09-01-2015 02:11 PM
Copy and Paste from File to File but File Names always change Copy/Paste from Paint to Word - There is a problem saving the file one2three Word 1 10-26-2011 07:35 AM
Copy and Paste from File to File but File Names always change 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 10:19 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft