Hello bigme,
Thanks for reaching us. I understand that you want to copy and paste rows that contain the same customer name into new files that are named after customer names.
We can apply a VBA Macro to achieve that. From your “source” Workbook, press
Alt + F11 to open the
Visual Basic Editor. Afterward,
Insert a
Module and enter the following script:
Code:
Private Function UniqueValues(arr)
Dim ret() As Variant
ReDim ret(1 To 1)
ret(1) = arr(1)
For i = 2 To UBound(arr)
test = False
For j = 1 To UBound(ret)
If arr(i) = ret(j) Then
test = True
Exit For
End If
Next j
If test = False Then
ReDim Preserve ret(1 To UBound(ret) + 1)
ret(UBound(ret)) = arr(i)
End If
Next i
UniqueValues = ret
End Function
Sub CopyDataToIndividualWB()
Dim i As Integer
Dim j As Integer
Dim lastRow As Integer
Dim cRow As Integer
Dim sum As Variant
Dim flPath As String
Set sourceSheet = ThisWorkbook.Worksheets("Unit")
lastRow = sourceSheet.Cells(Rows.Count, 2).End(xlUp).Row
Set DataRange = sourceSheet.Range("A2:H" & lastRow)
Dim custArr() As Variant
Dim unqCustArr() As Variant
ReDim custArr(1 To DataRange.Rows.Count)
ReDim unqCustArr(1 To DataRange.Rows.Count)
For i = 1 To UBound(custArr)
custArr(i) = DataRange.Cells(i, 2).Value
Next i
unqCustArr = UniqueValues(custArr)
For i = 1 To UBound(unqCustArr)
'Change the file path according to your computer files
flPath = "C:\Users\Seemanto Saha\Pictures\Forum Reply\FQ-124\"
Set wb = Workbooks.Add
wb.SaveAs flPath & unqCustArr(i) & ".xlsx"
sourceSheet.Range("A1:H1").Copy wb.Sheets(1).Cells(1, 1)
cRow = 2
sum = 0
For j = 1 To DataRange.Rows.Count
If DataRange.Cells(j, 2) = unqCustArr(i) Then
DataRange.Rows(j).Copy wb.Sheets(1).Cells(cRow, 1)
sum = sum + DataRange.Cells(j, DataRange.Columns.Count).Value
cRow = cRow + 1
End If
Next j
wb.Sheets(1).Cells(cRow, DataRange.Columns.Count).Value = sum
wb.Sheets(1).Cells.EntireColumn.AutoFit
wb.Save
wb.Close
Next i
End Sub
Don’t forget to change the file path according to your computer file paths. Now, Save and Run the macro.
Now, if you update the “source” sheet and want to update the other sheets dynamically, you can apply any of the following two methods:
Method-1:
Delete the previous workbooks and execute the previous VBA macro again.
Method-2:
We can apply another VBA macro incorporating a
UserForm. For that, from your “source” Workbook, press
Alt + F11 to open the
Visual Basic Editor. Then Insert a
UserForm and drag a
Label, a
RefEdit, and a
CommandButton into the
UserForm.
Double-click on the
CommandButton and insert the following VBA macro:
Code:
Public Function UniqueValues(arr)
Dim ret() As Variant
ReDim ret(1 To 1)
ret(1) = arr(1)
For i = 2 To UBound(arr)
test = False
For j = 1 To UBound(ret)
If arr(i) = ret(j) Then
test = True
Exit For
End If
Next j
If test = False Then
ReDim Preserve ret(1 To UBound(ret) + 1)
ret(UBound(ret)) = arr(i)
End If
Next i
UniqueValues = ret
End Function
Private Sub CommandButton1_Click()
If UserForm1.RefEdit1.Value = "" Then
MsgBox "Please Select a Range."
Exit Sub
End If
Set sourceSheet = ThisWorkbook.Worksheets("Unit")
Set updateRng = Range(UserForm1.RefEdit1.Value)
If updateRng.Columns.Count < 8 Then
MsgBox "Please Select All The Columns in Update Range."
Exit Sub
End If
Dim i As Integer
Dim j As Integer
Dim nLastRow As Integer
Dim newSum As Variant
Dim cRow As Integer
Dim arrCust() As Variant
Dim arrUnqCust() As Variant
ReDim arrCust(1 To updateRng.Rows.Count)
ReDim arrUnqCust(1 To updateRng.Rows.Count)
For i = 1 To updateRng.Rows.Count
arrCust(i) = updateRng.Cells(i, 2).Value
Next i
arrUnqCust = UniqueValues(arrCust)
Dim flPath As String
For i = 1 To UBound(arrUnqCust)
flPath = "C:\Users\Seemanto Saha\Pictures\Forum Reply\FQ-124\" & arrUnqCust(i) & ".xlsx"
On Error Resume Next
Workbooks.Open flPath
If Err.Number <> 0 Then
MsgBox "Failed to open the workbook from the path " & flPath
End If
On Error GoTo 0
Set wb = Workbooks(arrUnqCust(i))
Set ws = wb.Sheets(1)
nLastRow = ws.Cells(Rows.Count, 2).End(xlUp).Row
cRow = nLastRow + 1
newSum = ws.Cells(cRow, 8).Value
ws.Cells(cRow, 8).Value = ""
For j = 1 To updateRng.Rows.Count
If updateRng.Cells(j, 2) = arrUnqCust(i) Then
updateRng.Rows(j).Copy ws.Cells(cRow, 1)
newSum = newSum + updateRng.Cells(j, updateRng.Columns.Count).Value
cRow = cRow + 1
End If
Next j
ws.Cells(cRow, updateRng.Columns.Count).Value = newSum
wb.Save
wb.Close
UserForm1.Hide
Next i
End Sub
Don’t forget to change the file path according to your computer file locations. Now, let’s assume we added the following new Rows in the ‘source’ sheet.
Now,
Run the macro used in the
UserForm. A form like the following will appear. Insert the range to be updated and press the
Update button.
The required workbooks will be updated. I’ll attach the ‘source’ file in the attachments.
I hope this will be helpful for your problem. Let us know your feedback.
Regards,
Seemanto Saha
ExcelDemy
