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
Hi,
This looks like a job for Power Query. If you can provide some sample files with the source data then I can write a query for you.
regards
Phil
Sir i would like to request in this regards that please make this via VBA.
Hi,
Why do more work than you need to? If Power Query can do this then that's what I'd use. If PQ can't do it then we can look at a VBA solution.
Regards
Phil
