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:

DMTITL	DHACCT	DHDATE	DHDATC	DHITC	DHAMT	DESC1	DESC2
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019189	70819	55	259	Testing	Test Company
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019189	70819	55	499	Testing	Test Company
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019189	70819	55	2795.81	Testing	Test Company
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019189	70819	55	6000	Testing	Test Company
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019189	70819	55	6500	Testing	Test Company
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019190	70919	55	114	Testing	Test Company
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019191	71019	18	834.47	Testing	Test Company
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019191	71019	18	1590.7	Testing	Test Company
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019191	71019	18	2609.02	Testing	Test Company
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019191	71019	18	3294.32	Testing	Test Company
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019191	71019	18	4632.49	Testing	Test Company
SIGNAPAY LTD IN PROCESS ACCOUN	xxxxxxxxxx	2019191	71019	18	6667.57	Testing	Test Company

<tbody>
</tbody>

 

[babar2019]

HAPPY NEW YEAR...

I just gave it a try with the above code and It prompts for every single file actually which is a little bit cumbersome to the user.

Also, when I input the first business day as 010220, it did not copy paste the last month's data into the new month's tab. It just copy/pasted today's data into the 0120 tab.

As I mentioned earlier, I think it would be better to add a one time message box at the beginning of the macro asking '‘Do you want to roll over the month end data?’ with a Yes or No option.

If selected Yes, then the macro will simply copy paste last month's data into the new months's tab. In this case, it will copy 1219 over to 0120 tab and additionally add the new data into the 0120 tab.

If selected No, the macro will simply skip the roll over process and just run the file for today and copy paste into the current month's tab.

Basically the logic should be based on if the user will select Yes or No.

Thank you..

 

[babar2019]

Disregard this comment.

 

[babar2019]

@Admin Can someone please assist?

 

[babar2019]

@mumps Can you please look into this when you get a chance?