Change the part in red.
Worked. Kindly look into this small problem post #44. I apologies for any inconvenience caused.
Change the part in red.
Worked. Kindly look into this small problem post #44. I apologies for any inconvenience caused.
To clarify:
You now want the Master file to display only the total quantity in column H for each order number instead of all the data. Is this correct?
To clarify:
You now want the Master file to display only the total quantity in column H for each order number instead of all the data. Is this correct?
Yes. I want all the multiple entries as a single entry conditioning that the ColumnH "Order Quantity" should having a summation of all the multiple entries. Thankyou for all the help
To clarify:
You now want the Master file to display only the total quantity in column H for each order number instead of all the data. Is this correct?
As a single Order Number have multiple has many entries with the same order number, so i want it sort to one having the total order quantity
Try the attached file.
Try the attached file.
Worked. God bless you!
Glad it all worked out and thank you.
Copying of an addition of column is required
Hope you are doing well. a very small addition is required in this code as mentioned below right now it was matching the Ordernumber in ColumnC in "POD "file with ColumG in "masterfile" and pasting the respective POD in Column F of "Mastefile", I now want it to copy the data in ColumnE titled as "Exceptions" and paste it into ColumnL of masterfile keeping the format same like for matched order number its respective exceptions should be pasted in ColumnL of masterfile from ColumnE in file "POD"
Code of module POD is atatched for your reference and files are uploaded
Thankyou for all the help
Sub uploadPODdata()
Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, RngList As Object, key As Variant
Dim DRow As Long, cRow As Long, Lastrow As Long, fnd As Range, PO As Range, sAddr As String, arr As Variant, i As Long
Set desWB = ThisWorkbook
Set WSdest = desWB.Sheets(1)
Lastrow = WSdest.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen = False Then Exit Sub
Set OpenBook = Application.Workbooks.Open(FileToOpen)
With Sheets(1)
DRow = .Cells(.Rows.Count, "C").End(xlUp).Row
arr = .Range("C5:C" & DRow).Resize(, 2).Value
Set RngList = CreateObject("Scripting.Dictionary")
For i = LBound(arr) To UBound(arr)
If Not RngList.Exists(arr(i, 1)) Then
RngList.Add key:=arr(i, 1), Item:=arr(i, 2)
With WSdest.Cells(4, 1).CurrentRegion
.AutoFilter 7, arr(i, 1)
End With
With WSdest
.Range("F5:F" & Lastrow).SpecialCells(xlCellTypeVisible) = RngList(arr(i, 1))
End With
End If
Next i
WSdest.Range("A5").AutoFilter
End With
With WSdest
cRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("N5:N" & cRow).Formula = "=if(F5<=E5,M5*1,M5*0)"
End With
Application.ScreenUpdating = True
ActiveWorkbook.Close False
Application.CutCopyMode = False
End Sub
Display More
Glad it all worked out and thank you.
Kindly, look into this small problem aswell. thankyou and i wish you well
Glad it all worked out and thank you.
corrected POD file for ur reference.
Try:
Sub uploadPODdata()
Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, RngList As Object, key As Variant
Dim DRow As Long, cRow As Long, Lastrow As Long, fnd As Range, PO As Range, sAddr As String, arr As Variant, i As Long
Set desWB = ThisWorkbook
Set WSdest = desWB.Sheets(1)
Lastrow = WSdest.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen = False Then Exit Sub
Set OpenBook = Application.Workbooks.Open(FileToOpen)
With Sheets(1)
DRow = .Cells(.Rows.Count, "C").End(xlUp).Row
arr = .Range("C5:C" & DRow).Resize(, 3).Value
Set RngList = CreateObject("Scripting.Dictionary")
For i = LBound(arr) To UBound(arr)
If Not RngList.Exists(arr(i, 1)) Then
RngList.Add key:=arr(i, 1), Item:=arr(i, 2) & "|" & arr(i, 3)
With WSdest.Cells(4, 1).CurrentRegion
.AutoFilter 7, arr(i, 1)
End With
With WSdest
.Range("F5:F" & Lastrow).SpecialCells(xlCellTypeVisible) = Split(RngList(arr(i, 1)), "|")(0)
.Range("L5:L" & Lastrow).SpecialCells(xlCellTypeVisible) = Split(RngList(arr(i, 1)), "|")(1)
End With
End If
Next i
WSdest.Range("A5").AutoFilter
End With
With WSdest
cRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("N5:N" & cRow).Formula = "=if(F5<=E5,M5*1,M5*0)"
End With
Application.ScreenUpdating = True
ActiveWorkbook.Close False
Application.CutCopyMode = False
End Sub
Display More
Try:
CodeDisplay MoreSub uploadPODdata() Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, RngList As Object, key As Variant Dim DRow As Long, cRow As Long, Lastrow As Long, fnd As Range, PO As Range, sAddr As String, arr As Variant, i As Long Set desWB = ThisWorkbook Set WSdest = desWB.Sheets(1) Lastrow = WSdest.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Application.ScreenUpdating = False FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*") If FileToOpen = False Then Exit Sub Set OpenBook = Application.Workbooks.Open(FileToOpen) With Sheets(1) DRow = .Cells(.Rows.Count, "C").End(xlUp).Row arr = .Range("C5:C" & DRow).Resize(, 3).Value Set RngList = CreateObject("Scripting.Dictionary") For i = LBound(arr) To UBound(arr) If Not RngList.Exists(arr(i, 1)) Then RngList.Add key:=arr(i, 1), Item:=arr(i, 2) & "|" & arr(i, 3) With WSdest.Cells(4, 1).CurrentRegion .AutoFilter 7, arr(i, 1) End With With WSdest .Range("F5:F" & Lastrow).SpecialCells(xlCellTypeVisible) = Split(RngList(arr(i, 1)), "|")(0) .Range("L5:L" & Lastrow).SpecialCells(xlCellTypeVisible) = Split(RngList(arr(i, 1)), "|")(1) End With End If Next i WSdest.Range("A5").AutoFilter End With With WSdest cRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("N5:N" & cRow).Formula = "=if(F5<=E5,M5*1,M5*0)" End With Application.ScreenUpdating = True ActiveWorkbook.Close False Application.CutCopyMode = False End Sub
Worked once, the file is getting hanged for some reasons so not sure. Thanks for all the help!
My pleasure.
My pleasure.
@Mumps I would be really grateful if you could help me on this thread. Link attached hereby
Don’t have an account yet? Register yourself now and be a part of our community!