Copy from one source file into multiple excel files - All live in one location

babar2019

Board Regular
Joined
Jun 21, 2019
Messages
93
Hello ,

I have an excel file which is the source document and I have other excel files which are destination files all in one location.

- I want VBA to open the source excel and Format the Date in Column C of the source to MM YYYY.
- Check if column A has the word 'SIGNAPAY' ,
- If true then open a document called 'In process SIGNAPAY.xlsx' from that location,
- Click on the sheet name which matches with Column C (MM YYYY) of the source excel.
- Copy rows through columns A:G in the source excel for all rows which have SIGNAPAY in column A and paste into the last empty rows in the 'In Process SIGNAPAY.xlsx' workbook.

Below is what the data would look like:



DMTITLDHACCTDHDATEDHDATCDHITCDHAMTDESC1DESC2
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx20191897081955259TestingTest Company
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx20191897081955499TestingTest Company
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx201918970819552795.81TestingTest Company
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx201918970819556000TestingTest Company
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx201918970819556500TestingTest Company
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx20191907091955114TestingTest Company
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx20191917101918834.47TestingTest Company
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx201919171019181590.7TestingTest Company
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx201919171019182609.02TestingTest Company
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx201919171019183294.32TestingTest Company
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx201919171019184632.49TestingTest Company
SIGNAPAY LTD IN PROCESS ACCOUNxxxxxxxxxx201919171019186667.57TestingTest Company

<tbody>
</tbody>

 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
If you want to open the source file as well as the destination file, that would mean that the macro would have to be placed in a third file. Is that what you want to do or do you want to open the source file manually and place the macro in the source file which would then open the destination file? Is the source file in the same folder as the destination files? What is the name of the sheet in the sheet containing the data in the source file and what is the name of the destination sheet in the destination file? Do you have only one destination file (SIGNAPAY.xlsx) or more than one? If more than one, then how do you determine which one you want to open?
 
Upvote 0
Thank you for your response, mumps. Please see responses below.

-I would open the source file manually every time and run the macro using a shortcut.
-I would want the destination files open in the background and do it's job. I just think it will be overwhelming to the user to watch multiple files open and close. They would freak out.
-Yes, source files is in the same folder as the destination files.
-The sheet name of the source file would always be 'QRYLIBA380.CSIPHIST>Sheet1'
-The sheet name of the destination files are named in the form of 'MMYY'. The macro should be able to figure out which sheet it should click on based on column C of the source file. If Column C says 2019912, it should go to sheet '0919'.
-There are multiple destination files. I would like the macro to open the destination file based on Column A value. If column A in the source files has the word 'SIGNAPAY" for cells A2:A89, It should copy columns A2:A89 through G2:G89, open a file called 'In Process SIGNAPAY.xlsx' and paste it into the last empty row of the destination file.


Similaryly, Itf column A has the word 'APS', copy cell values through columns G for all rows in A which has the word APS and open the 'In Process APS.xlsx' and paste it into the last empty row of the destination file.

I can send you the chart if you'd like as to which unique word in column A of the source file should open which destination file.

Thank you in advance.
 
Upvote 0
Can you clarify the date format in column C? What dates do the following represent: 2019189, 2019190, 2019191 and what would the corresponding sheet names be?
 
Upvote 0
It would be easier to test a possible solution if you could upload a copy of your source file and at least one copy of a destination file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. If the workbooks contains confidential information, you could replace it with generic data.
 
Upvote 0
My bad. Just confirmed that the date column is actually D. So if it says 71819, the sheet name would be 0719. The sheet names are basically MMYY. So you can ignore the day.

I have laid down a chart Below to be more clear on what file the macro should exactly open based on the cell value in Column A. These are all exact destination file names and all are in xlsx format.

Column A Value
File to Open


SIGNAPAY LTD IN PROCESS ACCOUN
In Process DDA Recon - SignaPay
EPT 6001 IN PROCESS ACCOUNT
In Process DDA Recon - EPS
APS IN PROCESS ACCOUNT
In Process DDA Recon - APS
PAYMENT WORLD IN PROCESS ACCT
In Process DDA Recon - Payment World
TRISOURCE IN PROCESS ACCOUNT
In Process DDA Recon - TriSource
BANCTEK SOLUTIONS IN PROCESS
In Process DDA Recon - BancTek
MERCHANT BANCARD IN PROCESS
In Process DDA Recon - MBN
ADVANCE MERCHANT IN PROCESS AC
In Process DDA Recon - DAS
2C PROCESSOR IN PROCESS
In Process DDA Recon - 2CP
FRONTLINE IN PROCESS ACCOUNT
In Process DDA Recon - FrontLine
TITANIUM PROCESSING IN PROCESS
In Process DDA Recon - Titanium Processing
ARGUS MERCHANT IN PROCESS ACCT
In Process DDA Recon - Argus
INFINITY CAPTIAL LLC IN PROCES
In Process DDA Recon - Choice
TITANIUM PAYMENTS IN PROCESS
In Process DDA Recon - Titanium Payments
MERCHANT INDUSTR IN PROCESS
In Process DDA Recon - Merchant Industry
UNIFIED PAYMENTS IN PROCESS
In Process DDA Recon - Unified
ELECTRONIC MERCHANT SYS IN PRO
In Process DDA Recon - EMS Conversion
MAVERICK IN PROCESS ACCOUNT
In Process DDA Recon - Maverick
PIVOTAL PAYMENTS IN PROCESS
In Process DDA Recon - Nuvei
C&H FINANCIAL SERVICES IN PROC
In Process DDA Recon - C&H
MERCHANT LYNX SERVICES IN PROC
In Process DDA Recon - Merchant Lynx

<tbody>
</tbody>
 
Upvote 0
Could you upload the files as I suggested in Post #5 ?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top