I have a Workbooks("Serial Number").Sheet1 where Column B contains data such as 0001, 0002 , 0003 , 0004 upto 9999 row wise and other Workbooks("Data").Sheet1 have data where ColA has such values 

 

205-371-

205-372-
205-373-
205-374- till used range

row wise

and i am looking to merge the data like below example on the Workbooks("Data").Sheet1 ColA.

205-371-0001
205-371-0002
205-371-0003
205-371-0004

The result will be as like picture.

 

 

after that wants to add the loop in the formula section in below code.

m xFdItem As Variant

Dim xFileName As String

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Set xFd = Application.FileDialog(msoFileDialogFolderPicker)

If xFd.Show = -1 Then

 xFdItem = xFd.SelectedItems(1) & Application.PathSeparator

 xFileName = Dir(xFdItem & "*.csv*")

End If

 

Wants to add here a concatenate formula with Do While Loop for all sheets a folder have. 

 

Dim Wkb As Workbook

With Application

.ScreenUpdating = False

For Each Wkb In Workbooks

With Wkb

If Not Wkb.ReadOnly Then

.Save

End If

If .Name <> ThisWorkbook.Name Then

.Close

End If

End With

Next Wkb

.ScreenUpdating = True

    End With

End Sub