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

[babar2019]

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:



[TABLE="class: cms_table_cms_table_cms_table, width: 1031"]
<tbody>[TR]
[TD]DMTITL[/TD]
[TD]DHACCT[/TD]
[TD]DHDATE[/TD]
[TD]DHDATC[/TD]
[TD]DHITC[/TD]
[TD]DHAMT[/TD]
[TD]DESC1[/TD]
[TD]DESC2[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019189[/TD]
[TD="align: right"]70819[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]259[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019189[/TD]
[TD="align: right"]70819[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]499[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019189[/TD]
[TD="align: right"]70819[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]2795.81[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019189[/TD]
[TD="align: right"]70819[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]6000[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019189[/TD]
[TD="align: right"]70819[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]6500[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019190[/TD]
[TD="align: right"]70919[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]114[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019191[/TD]
[TD="align: right"]71019[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]834.47[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019191[/TD]
[TD="align: right"]71019[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1590.7[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019191[/TD]
[TD="align: right"]71019[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]2609.02[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019191[/TD]
[TD="align: right"]71019[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]3294.32[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019191[/TD]
[TD="align: right"]71019[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]4632.49[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN[/TD]
[TD]xxxxxxxxxx[/TD]
[TD="align: right"]2019191[/TD]
[TD="align: right"]71019[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]6667.57[/TD]
[TD]Testing[/TD]
[TD]Test Company[/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

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?

 

[babar2019]

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.

 

[mumps]

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?

 

[mumps]

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.

 

[babar2019]

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.

[TABLE="width: 744"]
<tbody>[TR]
[TD]Column A Value
[/TD]
[TD]File to Open
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]SIGNAPAY LTD IN PROCESS ACCOUN
[/TD]
[TD]In Process DDA Recon - SignaPay
[/TD]
[/TR]
[TR]
[TD]EPT 6001 IN PROCESS ACCOUNT
[/TD]
[TD]In Process DDA Recon - EPS
[/TD]
[/TR]
[TR]
[TD]APS IN PROCESS ACCOUNT
[/TD]
[TD]In Process DDA Recon - APS
[/TD]
[/TR]
[TR]
[TD]PAYMENT WORLD IN PROCESS ACCT
[/TD]
[TD]In Process DDA Recon - Payment World
[/TD]
[/TR]
[TR]
[TD]TRISOURCE IN PROCESS ACCOUNT
[/TD]
[TD]In Process DDA Recon - TriSource
[/TD]
[/TR]
[TR]
[TD]BANCTEK SOLUTIONS IN PROCESS
[/TD]
[TD]In Process DDA Recon - BancTek
[/TD]
[/TR]
[TR]
[TD]MERCHANT BANCARD IN PROCESS
[/TD]
[TD]In Process DDA Recon - MBN
[/TD]
[/TR]
[TR]
[TD]ADVANCE MERCHANT IN PROCESS AC
[/TD]
[TD]In Process DDA Recon - DAS
[/TD]
[/TR]
[TR]
[TD]2C PROCESSOR IN PROCESS
[/TD]
[TD]In Process DDA Recon - 2CP
[/TD]
[/TR]
[TR]
[TD]FRONTLINE IN PROCESS ACCOUNT
[/TD]
[TD]In Process DDA Recon - FrontLine
[/TD]
[/TR]
[TR]
[TD]TITANIUM PROCESSING IN PROCESS
[/TD]
[TD]In Process DDA Recon - Titanium Processing
[/TD]
[/TR]
[TR]
[TD]ARGUS MERCHANT IN PROCESS ACCT
[/TD]
[TD]In Process DDA Recon - Argus
[/TD]
[/TR]
[TR]
[TD]INFINITY CAPTIAL LLC IN PROCES
[/TD]
[TD]In Process DDA Recon - Choice
[/TD]
[/TR]
[TR]
[TD]TITANIUM PAYMENTS IN PROCESS
[/TD]
[TD]In Process DDA Recon - Titanium Payments
[/TD]
[/TR]
[TR]
[TD]MERCHANT INDUSTR IN PROCESS
[/TD]
[TD]In Process DDA Recon - Merchant Industry
[/TD]
[/TR]
[TR]
[TD]UNIFIED PAYMENTS IN PROCESS
[/TD]
[TD]In Process DDA Recon - Unified
[/TD]
[/TR]
[TR]
[TD]ELECTRONIC MERCHANT SYS IN PRO
[/TD]
[TD]In Process DDA Recon - EMS Conversion
[/TD]
[/TR]
[TR]
[TD]MAVERICK IN PROCESS ACCOUNT
[/TD]
[TD]In Process DDA Recon - Maverick
[/TD]
[/TR]
[TR]
[TD]PIVOTAL PAYMENTS IN PROCESS
[/TD]
[TD]In Process DDA Recon - Nuvei
[/TD]
[/TR]
[TR]
[TD]C&H FINANCIAL SERVICES IN PROC
[/TD]
[TD]In Process DDA Recon - C&H
[/TD]
[/TR]
[TR]
[TD]MERCHANT LYNX SERVICES IN PROC
[/TD]
[TD]In Process DDA Recon - Merchant Lynx
[/TD]
[/TR]
</tbody>[/TABLE]

 

[babar2019]

Wonderful. I can share it via Box.

What's your email?

 

[mumps]

Could you upload the files as I suggested in Post #5 ?

 

[babar2019]

I sure can. I will share the file URL's shortly.

 

[babar2019]

https://esquirebank.box.com/s/jzhjh2ig5jrv0d3qlp5t312nvdqwe86k


https://esquirebank.box.com/s/lijn8aj3nr04sltvs1m5t4vuugf13p30


https://esquirebank.box.com/s/dydl0603g6zxjq91ti77xarnprqql8kw