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

babar2019 · Sep 12, 2019

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 · Sep 20, 2019

Replace the code:

Code:

With desWS
    .Range("G12:I12").Copy
    .Range("G13:I" & totals2 - 1).PasteSpecial Paste:=xlPasteFormulas
End With

with this:

Code:

With desWS
    .Range("G12:I12").Copy
    .Range("G13:I" & totals2 - 1).PasteSpecial Paste:=xlPasteFormulas
    .Range("F" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""F12:F""&ROW()-1))"
    .Range("G" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""G12:G""&ROW()-1))"
    .Range("H" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""H12:H""&ROW()-1))"
    .Range("I" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""I12:I""&ROW()-1))"
End With

babar2019 · Sep 23, 2019

Hello,

I was testing it on multiple destination workbooks this morning and it seems to only work fine on the first one and errors out on the next.

The error that I'm getting is 'Object variable or With block variable not set'.

At that point when I go to the source file, it has weird filters applied. I remove all the filters and try again but it does not work.

Below is the link to the files that I was testing on:
https://esquirebank.box.com/s/n3wx9hhglapllwq37dwltpepwk55etwa

Also, I noticed this morning that a few destination workbooks have Column E as DR/CR instead of codes 55,18,78,38.
I spoke to our users about being consistent and they said they would go with DR/CR.

Can the macro convert codes from the source files to DR/CR ?

For example,

If source file Column E is 55 or 78, the destination workbook would populate as DR in Column E.
If source file Column E is 18 or 38, the destination workbook would populate as CR in Column E.

Currently is is pasting as it is from source, But I would like this conversion to make their process consistent.

Sorry! Something or the other comes up in testing.

Thanks in advance.

mumps · Sep 24, 2019

All the sheets in most of the destination files have the word "Reconciliation" in the third last cell in column C spelled wrong. You will have to check all the sheets in all the destination files and make the correction. Then try the revised macro.

Code:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook, srcWS As Worksheet, desWS As Worksheet, LastRow As Long, key1 As Variant, key2 As Variant, totals1 As Long, totals2 As Long
    Dim RngList1 As Object, RngList2 As Object, rng As Range, rng1 As Range, rng2 As Range, arr As Variant, i As Long, fNames As String, code As Variant
    Set srcWS = ThisWorkbook.Sheets("QRYLIBA380.CSIPHIST>Sheet1")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    fNames = "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"
        arr = Split(Application.Trim(fNames), ",")
    Set RngList1 = CreateObject("Scripting.Dictionary")
    Set RngList2 = CreateObject("Scripting.Dictionary")
    For Each rng1 In srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
        If Not RngList1.Exists(rng1.Value) Then
            RngList1.Add rng1.Value, Nothing
        End If
    Next rng1
    For Each key1 In RngList1
        For i = 0 To UBound(arr)
            If arr(i) = key1 Then
                Set wkbDest = Workbooks.Open(ThisWorkbook.Path & "\" & arr(i + 1) & ".xlsx")
                With srcWS.Cells(1).CurrentRegion
                    .AutoFilter 1, key1
                    For Each rng2 In srcWS.Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible)
                        If Not RngList2.Exists(rng2.Value) Then
                            RngList2.Add rng2.Value, Nothing
                        End If
                    Next rng2
                    For Each key2 In RngList2
                        Set desWS = ActiveWorkbook.Sheets(Format(DateSerial(Right(key2, 2), Left(key2, Len(key2) - 4), Mid(key2, Len(key2) - 3, 2)), "mmyy"))
                        With srcWS.Cells(1).CurrentRegion
                            .AutoFilter 4, key2
                            totals1 = desWS.Range("C:C").Find("Reconciliation Totals").Row
                            RowCount = srcWS.[subtotal(103,A:A)] - 1
                            desWS.Cells(totals1, 1).EntireRow.Resize(RowCount).Insert Shift:=xlDown
                            srcWS.Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(totals1, 2)
                            totals2 = desWS.Range("C:C").Find("Reconciliation Totals").Row
                            For Each rng In desWS.Range("B" & totals1 & ":B" & totals2 - 1)
                                rng = Format(DateSerial(Right(rng, 2), Left(rng, Len(rng) - 4), Mid(rng, Len(rng) - 3, 2)), "mm/dd/yy")
                            Next rng
                            With srcWS
                                .Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(totals1, 5)
                                .Range("F2:F" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(totals1, 6)
                                .Range("G2:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(totals1, 3)
                                .Range("H2:H" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(totals1, 4)
                            End With
                        End With
                        srcWS.Cells(1).AutoFilter
                    Next key2
                    totals2 = desWS.Range("C:C").Find("Reconciliation Totals").Row
                    With desWS.Range("B12:B" & totals2 - 1)
                        .Font.Name = "Bookman Old Style"
                        .Font.Color = 10040115
                        .Font.Size = 10
                        .HorizontalAlignment = xlLeft
                    End With
                    With desWS.Range("C12:C" & totals2 - 1)
                        .Font.Name = "Bookman Old Style"
                        .Font.Size = 10
                        .HorizontalAlignment = xlLeft
                    End With
                    With desWS.Range("D12:D" & totals2 - 1)
                        .Font.Name = "Bookman Old Style"
                        .Font.Color = 10040115
                        .Font.Size = 10
                        .HorizontalAlignment = xlLeft
                    End With
                    With desWS.Range("E12:E" & totals2 - 1)
                        .Font.Name = "Bookman Old Style"
                        .Font.Color = 10040115
                        .Font.Size = 10
                        .HorizontalAlignment = xlCenter
                        .Replace "55", "DR"
                        .Replace "78", "DR"
                        .Replace "18", "CR"
                        .Replace "38", "CR"
                    End With
                    With desWS.Range("F12:F" & totals2 - 1)
                        .Font.Name = "Bookman Old Style"
                        .Font.Color = 10040115
                        .Font.Size = 10
                    End With
                    With desWS
                        For Each code In .Range("E12:E" & totals2 - 1)
                            If code = "DR" Then
                                If code.Offset(, 1) > 0 Then
                                    code.Offset(, 1) = "-" & code.Offset(, 1)
                                End If
                                code.Offset(, 1).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
                            ElseIf code = "CR" Then
                                code.Offset(, 1).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
                            End If
                        Next code
                    End With
                    With desWS
                        .Range("G12:I12").Copy
                        .Range("G13:I" & totals2 - 1).PasteSpecial Paste:=xlPasteFormulas
                        .Range("F" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""F12:F""&ROW()-1))"
                        .Range("G" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""G12:G""&ROW()-1))"
                        .Range("H" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""H12:H""&ROW()-1))"
                        .Range("I" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""I12:I""&ROW()-1))"
                    End With
                End With
            End If
        Next i
        RngList2.RemoveAll
        'wkbDest.Close True
    Next key1
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

babar2019 · Sep 25, 2019

Hello,

Thanks for your response. I noticed that soon as I commented and corrected all the destination workbooks.

This is probably the last thing that I would request, I don't know why filters are applied to the date column which is Column D of the source file and then the macro is copying all the rows. It should not do that.

The uniqueness of the source file is totally dependant on column A.

For example, if Column A in the source file rows 2-20 has the value 'SIGNAPAY LTD IN PROCESS ACCOUN', then it should copy all the required info for rows 2 -20 and paste it into the specific destination workbook.

Right now everything is working great except for the part where the filter is being added before pasting. What is happening is it is pasting data of one workbook into another.

I'm sorry if I didn't explain to you properly earlier as the process is very complex.

mumps · Sep 25, 2019

The reason it is filtering on column D is in case there are different months for the same account in column A which would put different rows for the same account in different sheets in the destination files. Are you saying that the month for each account in column A will always be the same? In any event, the filtering on column D shouldn't make any difference in the end result. I can modify the macro not to filter if the month for the same account will always be the same. Please advise.

babar2019 · Sep 26, 2019

Good morning,

That is correct. The month for every account in Column A will always be the same. The reason why I'm asking to remove the filters is because when the filter is added, it does not look into unique accounts in column A to copy/paste. It just copies everything and pastes in to destination workbooks.

I tested and ended up having data in wrong destination workbooks. Can you please remove the filter and I can test it one last time?

Thank you

mumps · Sep 26, 2019

Try:

Code:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook, srcWS As Worksheet, desWS As Worksheet, LastRow As Long, key As Variant, totals1 As Long, totals2 As Long, fVisRow As Long
    Dim RngList As Object, rng As Range, arr As Variant, i As Long, fNames As String, code As Variant, sDate As String
    Set srcWS = ThisWorkbook.Sheets("QRYLIBA380.CSIPHIST>Sheet1")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    fNames = "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"
        arr = Split(Application.Trim(fNames), ",")
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each rng In srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp))
        If Not RngList.Exists(rng.Value) Then
            RngList.Add rng.Value, Nothing
        End If
    Next rng
    For Each key In RngList
        For i = 0 To UBound(arr)
            If arr(i) = key Then
                Set wkbDest = Workbooks.Open(ThisWorkbook.Path & "\" & arr(i + 1) & ".xlsx")
                With srcWS.Cells(1).CurrentRegion
                    .AutoFilter 1, key
                    fVisRow = srcWS.Range("A1", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
                    sDate = srcWS.Cells(fVisRow, 4)
                    Set desWS = ActiveWorkbook.Sheets(Format(DateSerial(Right(sDate, 2), Left(sDate, Len(sDate) - 4), Mid(sDate, Len(sDate) - 3, 2)), "mmyy"))
                    totals1 = desWS.Range("C:C").Find("Reconciliation Totals").Row
                    RowCount = srcWS.[subtotal(103,A:A)] - 1
                    desWS.Cells(totals1, 1).EntireRow.Resize(RowCount).Insert Shift:=xlDown
                    srcWS.Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(totals1, 2)
                    totals2 = desWS.Range("C:C").Find("Reconciliation Totals").Row
                    For Each rng In desWS.Range("B" & totals1 & ":B" & totals2 - 1)
                        rng = Format(DateSerial(Right(rng, 2), Left(rng, Len(rng) - 4), Mid(rng, Len(rng) - 3, 2)), "mm/dd/yy")
                    Next rng
                    With srcWS
                        .Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(totals1, 5)
                        .Range("F2:F" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(totals1, 6)
                        .Range("G2:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(totals1, 3)
                        .Range("H2:H" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(totals1, 4)
                    End With
                    srcWS.Cells(1).AutoFilter
                    totals2 = desWS.Range("C:C").Find("Reconciliation Totals").Row
                    With desWS.Range("B12:B" & totals2 - 1)
                        .Font.Name = "Bookman Old Style"
                        .Font.Color = 10040115
                        .Font.Size = 10
                        .HorizontalAlignment = xlLeft
                    End With
                    With desWS.Range("C12:C" & totals2 - 1)
                        .Font.Name = "Bookman Old Style"
                        .Font.Size = 10
                        .HorizontalAlignment = xlLeft
                    End With
                    With desWS.Range("D12:D" & totals2 - 1)
                        .Font.Name = "Bookman Old Style"
                        .Font.Color = 10040115
                        .Font.Size = 10
                        .HorizontalAlignment = xlLeft
                    End With
                    With desWS.Range("E12:E" & totals2 - 1)
                        .Font.Name = "Bookman Old Style"
                        .Font.Color = 10040115
                        .Font.Size = 10
                        .HorizontalAlignment = xlCenter
                        .Replace "55", "DR"
                        .Replace "78", "DR"
                        .Replace "18", "CR"
                        .Replace "38", "CR"
                    End With
                    With desWS.Range("F12:F" & totals2 - 1)
                        .Font.Name = "Bookman Old Style"
                        .Font.Color = 10040115
                        .Font.Size = 10
                    End With
                    With desWS
                        For Each code In .Range("E12:E" & totals2 - 1)
                            If code = "DR" Then
                                If code.Offset(, 1) > 0 Then
                                    code.Offset(, 1) = "-" & code.Offset(, 1)
                                End If
                                code.Offset(, 1).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
                            ElseIf code = "CR" Then
                                code.Offset(, 1).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
                            End If
                        Next code
                    End With
                    With desWS
                        .Range("G12:I12").Copy
                        .Range("G13:I" & totals2 - 1).PasteSpecial Paste:=xlPasteFormulas
                        .Range("F" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""F12:F""&ROW()-1))"
                        .Range("G" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""G12:G""&ROW()-1))"
                        .Range("H" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""H12:H""&ROW()-1))"
                        .Range("I" & totals2).FormulaR1C1 = "=SUM(INDIRECT(""I12:I""&ROW()-1))"
                    End With
                End With
            End If
        Next i
        wkbDest.Close True
    Next key
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

babar2019 · Sep 26, 2019

Hi, That worked perfect.

Thank you very very much for your time and efforts on this

Really appreciate it.

mumps · Sep 27, 2019

Glad it worked out.

babar2019 · Sep 27, 2019

I'm glad too.. This morning we were testing and when we ran it the 1st time, it seemed to have worked.

When I looked into the destination workbooks, somehow 2 of the workbooks didn't update and I looked into the code and it was missing a comma(,).

I reran it again and everything worked fine. But what I noticed was it duplicated the sheets again by pasting the same values.

Technically they should not be running this multiple times in a day, but in case the macro fails to update any of the destination workbooks the first time and they rerun, is there a way to check that it was pasted on the workbook and just skip? This is worst case but just asking.

Thank you

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

babar2019

Board Regular

mumps

Well-known Member

Excel Facts

babar2019

Board Regular

mumps

Well-known Member

babar2019

Board Regular

mumps

Well-known Member

babar2019

Board Regular

mumps

Well-known Member

babar2019

Board Regular

mumps

Well-known Member

babar2019

Board Regular

Similar threads

Forum statistics

Share this page

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

Board Regular

Well-known Member

Excel Facts

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock