Excel VBA merge csv and sum values

Question

I have a need to merge a list of reports in the following format

Column 1 is a number
Column 2 is a string
Max rows is dynamic

In such a fashion as to sum the numbers in column 1 for strings that match (removing duplicates)

Currently I am merging the csv files with this

Dim files() As String
files = AllFiles(GetFolder) 'returns array of files in folder
Dim i As Long
For i = 0 To UBound(files)
    Windows("Monthly KB Page Access Report Generator.xls").Activate
    ActiveCell.Value = files(i)
    wrkbookvar2 = Dir(files(i))
    ActiveCell.Offset(1, 0).Activate
    Workbooks.Open Filename:=GetFolder & "\" & files(i)
    Set dataset_workbook = ActiveWorkbook
    Range(ActiveCell.SpecialCells(xlLastCell), Cells(1)).Copy
    Windows("Monthly KB Page Access Report Generator.xls").Activate
    Cells(ActiveCell.SpecialCells(xlLastCell).Row, 1).Select
    ActiveSheet.Paste
    dataset_workbook.Close
Next

This merges with 2 issues

First cell of each report is overwritten with the reports filename
After opening each file it prompts the user saying the clipboard has large data in it, I want to avoid this

I think the following code might work to merge my data and sum values but I cannot be sure because I am currently type missmatching due to the reports filename showing up

' Sort
Windows("filename.xls").Activate
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending

' merge
LastRow = ActiveSheet.UsedRange.Rows.Count
Set r = ActiveSheet.UsedRange.Resize(1)
With Application.WorksheetFunction
     For iRow = 2 To LastRow
        If Cells(iRow, 2) = Cells(iRow + 1, 2) Then
            Cells(iRow, 1) = Cells(iRow, 1) + Cells(iRow + 1, 1)
            Rows(iRow + 1).Delete
        End If
     Next iRow
End With

Anybody mind helping me iron this out, I am terrible at VBA.

UPDATE: Number 1 was resolved by removing

Windows("Monthly KB Page Access Report Generator.xls").Activate
ActiveCell.Value = files(i)
wrkbookvar2 = Dir(files(i))
ActiveCell.Offset(1, 0).Activate

They were part of an older copy that I completely didnt notice.

Everything is working except I need to adjust my 'Merge to account for more then 2 instances of a value, right now it will only merge 2.

Jamie Bull · Accepted Answer · 2012-11-13 19:05:10Z

2

1) Add ThisWorkbook.Activate before ActiveCell.Value = files(i) to avoid the overwriting problem. ThisWorkbook always refers to the workbook in which the macro is running.

2) Add Application.CutCopyMode = False before dataset_workbook.Close to deal with the clipboard prompt issue.

edited Nov 13, 2012 at 19:05

answered Nov 13, 2012 at 17:59

Jamie Bull

13.6k18 gold badges80 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TetraFlash Over a year ago

Number 2 is resolved, however number 1 didnt work, seems ThisWorkbook doesnt have an ActiceCell.

Collectives™ on Stack Overflow

Excel VBA merge csv and sum values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related