Hi all,
I am searching multiple sheets for a string, once this string is found it copies it to the MergedData sheet.
In column 1 is a payment number for instance "50". Column 2 is a work order number "11111". Colums "3,4,5,6,7,8" or other information. "9,10,12" are £ values.
I have two problems that i need to rectify.
1) when the data is pasted into the MergedData Sheet, it pastes it in row 2 rather than row 1.
2) when this data is copied over there could be multiple instances on a work order number is column 2, i need to have a sumif to find any duplicates in column 2 and sum up columns "9,10 & 12".
I have managed to get the code for the SUMIF working but i cant seem to get it to work after the data has been copied over.
What I need to happen is, when you click the button it search for the string, and have it copy to the MergedData sheet, and on the same button click to perform the SUMIF function.
Any assistance you can provide is greatly appreciated!
Here is the code that i am using at the moment.
Thanks Aarron
Here is the code to search and copy the raw data.
Here is the code i have for the SUMIF function.
I am searching multiple sheets for a string, once this string is found it copies it to the MergedData sheet.
In column 1 is a payment number for instance "50". Column 2 is a work order number "11111". Colums "3,4,5,6,7,8" or other information. "9,10,12" are £ values.
I have two problems that i need to rectify.
1) when the data is pasted into the MergedData Sheet, it pastes it in row 2 rather than row 1.
2) when this data is copied over there could be multiple instances on a work order number is column 2, i need to have a sumif to find any duplicates in column 2 and sum up columns "9,10 & 12".
I have managed to get the code for the SUMIF working but i cant seem to get it to work after the data has been copied over.
What I need to happen is, when you click the button it search for the string, and have it copy to the MergedData sheet, and on the same button click to perform the SUMIF function.
Any assistance you can provide is greatly appreciated!
Here is the code that i am using at the moment.
Thanks Aarron
Here is the code to search and copy the raw data.
Code:
Private Sub CommandButton1_Click()Dim FirstAddress As String, WhatFor As String
Dim Cell As Range, Sheet As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
.CutCopyMode = False
End With
WhatFor = Sheets("SUB CON PAYMENT FORM").Range("L9")
Worksheets("MergedData").Cells.Clear
If WhatFor = Empty Then Exit Sub
For Each Sheet In Sheets
If Sheet.Name <> "SUB CON PAYMENT FORM" And Sheet.Name <> "MergedData" And Sheet.Name <> "Details" Then
With Sheet.Columns(1)
Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
Cell.EntireRow.Copy
ActiveWorkbook.Sheets("MergedData").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
End If
Next Sheet
Set Cell = Nothing
End SubHere is the code i have for the SUMIF function.
Code:
Sub combineduplicates() '### starts our macroApplication.ScreenUpdating = False '### Excel wont update its screen while executing this macro. This is a huge performace boost
Dim SUMcols() '### declare a second empty array for our sum columns
SUMcols() = Array(9, 10, 12) '### the second array stores the columns which should be summed up
'### the next line sets our range for searching dublicates. Starting at cell A2 and ending at the last used cell in column A
Set searchrange = Range([b2], Columns(2).Find(what:="*", after:=[b1], searchdirection:=xlPrevious))
For Each cell In searchrange '### now we start looping through each cell of our searchrange
Set search = searchrange.Find(cell, after:=cell, lookat:=xlWhole) '### searches for a dublicate. If no dub exists, it finds only itself
Do While search.Address <> cell.Address '### until we find our starting cell again, these rows are all dublicates
For i = 0 To UBound(SUMcols) '### loop through all columns for calculating the sum
'### next line sums up the cell in our starting row and its counterpart in its dublicate row
Cells(cell.Row, SUMcols(i)) = CDbl(Cells(cell.Row, SUMcols(i))) + CDbl(Cells(search.Row, SUMcols(i)))
Next i '### go ahead to the next column
search.EntireRow.Delete '### we are finished with this row. Delete the whole row
Set search = searchrange.Find(cell, after:=cell) '### and search the next dublicate after our starting row
Loop
Next '### from here we start over with the next cell of our searchrange
'### Note: This is a NEW unique value since we already deleted all old dublicates
Application.ScreenUpdating = True '### re-enable our screen updating
End Sub '### ends our macro
