Copy Paste data from one source document to multiple excel documents

[babar2019]

Hi,

I have an excel which is the source document containing the following data.

Column A - Process Name
Column B - Date
Column C-G - data

I want the code to

read the Process Name in column A (source) and open respective excel document (destination)
read the date in Column B (source) and go to that respective tab (destination)
copy values A through G columns (source) and paste in the last empty row in the document (destination).

For example, if the source document column A reads SIGNAPAY, Column B reads 070819 then the code should open an excel called SIGNAPAY, go to the 07 19 tab and go to the last empty row and copy columns A-G from the source document and paste it here and close the workbook.

Can somebody please help me with the code.

Thank you in advance

 

[JLGWhiz]

This will work only if your filenames in Column A are in the same directory as your source workbook.

Sub t()
Dim c As Range, sh As Worksheet, wb As Workbook, tb As String, fPath As String
fPath = ThisWorkbook.Path & "\"
Set sh = ActiveSheet
    For Each c In sh.Range("A2", sh.Cells(Rows.Count, 1).End(xlUp))
        Set wb = Workbooks.Open(fPath & c.Value & ".xlsx")
            tb = CStr(Left(c.Offset(, 1), 2) & " " & Right(c.Offset(, 1), 2))
            Intersect(wb.Sheets(tb).UsedRange, wb.Sheets(tb).Range("A:G")).Copy _
            sh.Cells(Rows.Count, 1).End(xlUp)(2)
            wb.Close False
    Next
End Sub

Because you are working with dates, and the configuration of the date in the source workbook is not the same as the sheet tab name date, you might encounter a problem with "Subscript out of Range" errors. I have tried to accomodate that, but without seeing your worksheets, it might not be enough. You can try it and post back with the results.

 

[babar2019]

Hi JLGWhiz,

Thanks for your response. This didn't work and giving 'out of subscript range' errors.

The column A is not directly the file name but just the name of the process.

All I'm trying to do is, See if column A has the word 'Signapay' then open a workbook called In Process SIGNAPAY, see how many rows in column A have the word signapay and copy columns A:G and paste it into the In Process SIGNAPAY workbook.

 

[JLGWhiz]

You need to post a facimile of your worksheets, or a link to your files on a share server. I don't understand how your data in column A is configured.

 

[babar2019]

Please see below. Copy pasted directly from excel.

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

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[JLGWhiz]

I am sorry, but there appears to be a lot of inconsistency between the original post and the subsequent posts related to the details of the data and files. I cannot decipher the actual requirement nor how to determine the files and worksheets. Perhaps someone else can do better.

 

[babar2019]

Hi JLGWhiz,

Sorry If the post was confusing.

So the data you see above is the source document.

I want VBA to see if the document has the word 'SIGNAPAY' in column A, if true then open a document called 'In process SIGNAPAY.xlsx' from a location.
Format the Date in Column C of the source to MM YYYY and go into the tab which matches with the MM YYYY of the source column with the 'In Process SIGNAPAY.xlsx' workbook.
Paste values into the new cells 'In Process SIGNAPAY.xlsx' workbook for all the rows that have the word SIGNAPAY through columns A:G.

I hope I was clear