I have multiple Excel Workbooks (File 1, File 2,…), each of which has 2 common sheets - Sheet1 and Sheet2.

I want to merge Sheet1 from all Workbooks into a single Sheet1 (new Workbook)

(1) The column headers and sequence of all Shee1 are exactly the same.

(2) I only want to copy selected columns from all Sheet1 i.e. A, D, G, H, I,… into corresponding A D G H I… columns of combined Sheet1.

(3) I want to start from exactly the 7th row of each Sheet1 and then end with the row that has some data in Column A i.e. When the first blank in Column A is reached, the loop should move to the next Sheet1 in another Workbook

(4) The copied columns have to be pasted into the corresponding columns of the combined Sheet1. This combined file already has some formulae in the other Columns and hence this pasting from Table to combined Sheet1 has to be correctly done.

I have presently written a VBA Code for this which I run in the Workbook where I want the Combined data. I am pasting the same below. I want to replace this MACRO with KNIME, if possible.

Private Sub CommandButton1_Click()

Dim FolderPath As String

Dim FileName As String

Dim i As Long

Dim lcurrentrow As Long

Dim lrow As Long

Dim wb As Workbook

Dim list_file As Workbook

FolderPath = Application.ThisWorkbook.Path & “”

Set list_file = Workbooks.Open(FolderPath & “List of project files.xlsx”)

lcurrentrow = 7 'Row number in destination sheet at which pasting has to start. anything above this is left as it is

MsgBox (“Open List File”)

For i = 1 To 6 Step 1 'i points to Row in the “List of project files.xlsx” and must be equal to the number of files being consolidated

```
FileName = list_file.Sheets("Sheet1").Range("B" & i + 1).Value
Set wb = Workbooks.Open(FolderPath & FileName)
```

MsgBox (FileName)

With wb.Sheets(“Cash-in Data”) 'select the sheet from the source workbook that needs to be copied

```
lrow = 7 'Row number in source sheet from where copying has to start
Do Until .Range("E" & lrow).Value = vbNullString
For n = 0 To 68 Step 1
If (n = 0 Or n = 1 Or n = 2 Or n = 3 Or n = 4 Or n = 5 Or n = 7 Or n = 8 _
Or n = 10 Or n = 11 Or n = 13 Or n = 16 Or n = 17 Or n = 18 Or n = 20 _
Or n = 23 Or n = 24 Or n = 26 Or n = 27 Or n = 28 Or n = 30 Or n = 31 Or n = 32 _
Or n = 49 Or n = 53 Or n = 54 Or n = 61 Or n = 65 Or n = 66 Or n = 67 Or n = 68) Then
'select those columns from where we want to copy data
ThisWorkbook.Worksheets("Cash-in Data").Range("A" & lcurrentrow).Offset(columnoffset:=n).Value = .Range("A" & lrow).Offset(columnoffset:=n).Value
End If
Next n
lcurrentrow = lcurrentrow + 1
lrow = lrow + 1
Loop
End With
wb.Close True 'close the source workbook
```

Next i

list_file.Close True 'close the source workbook

Set wb = Nothing 'free the momory

Set list_file = Nothing 'free the momory

Sheets(“Commands”).Activate

ActiveSheet.Cells(1, 1).Select

End Sub