I have an excel spreadsheet (Excel 2016) with a number of sheets (Local Suburbs). Sheet1 (called "DataInput") collects data from outside the sheet and laying it in a row (basically I copy from another source and drop it there). Now column B has a heading "Unique Number" at B1 with the data starting in Row 2 - This number is as headed different for each row of data.
Also there is a column headed up "Hub location". This is column "H".
My problem is I wish to add any row of data that is added into the "DataInput" sheet across to sheet2 (Kariong) based on the following criteria: -
* Add to the next row after that one that has data in it;
* Only do so if that row of data is not already there; and
* Only transfer rows that relate to the suburb "Kariong" as shown in column H.
I have been able to get the first two done via Command Button with the following code but not to sort out any rows that doesn't relate to "Kariong". I will replicate this button for other suburbs on their individual sheets unless there is an easier way (total suburbs would be ten). I thought if I could get one right the reat can be adapted easy enough.
I must admit I adapted this code from another example and whilst it works I am not sure about the "found.offset" portion as the original code had different criteria it was searching for
Any help you can give will be most appreciated. Thanking you in advance
Also there is a column headed up "Hub location". This is column "H".
My problem is I wish to add any row of data that is added into the "DataInput" sheet across to sheet2 (Kariong) based on the following criteria: -
* Add to the next row after that one that has data in it;
* Only do so if that row of data is not already there; and
* Only transfer rows that relate to the suburb "Kariong" as shown in column H.
I have been able to get the first two done via Command Button with the following code but not to sort out any rows that doesn't relate to "Kariong". I will replicate this button for other suburbs on their individual sheets unless there is an easier way (total suburbs would be ten). I thought if I could get one right the reat can be adapted easy enough.
VBA Code:
Private Sub CommandButton3_Click()
Dim ws2 As Worksheet, ws1 As Worksheet
Dim cell As Range, Found As Range
Dim FirstFound As String
Dim bCopyInv As Boolean
Dim counter As Long
Set ws2 = Sheets("Kariong")
Set ws1 = Sheets("DataInput")
For Each cell In ws1.Range("b1", ws1.Range("b" & Rows.Count).End(xlUp))
bCopyInv = True
Set Found = ws2.Columns("b").Find(What:=cell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Found Is Nothing Then
FirstFound = Found.Address
Do
If Found.Offset(0, 2).Value = cell.Offset(0, 2).Value Then
bCopyInv = False
Exit Do
End If
Set Found = ws2.Columns("b").FindNext(after:=Found)
Loop Until Found.Address = FirstFound
End If
If bCopyInv Then
cell.EntireRow.Copy Destination:=ws2.Range("A" & Rows.Count).End(xlUp).Offset(1)
counter = counter + 1
End If
Next cell
MsgBox counter & " orders copied.", vbInformation, "Orders Copy Complete"
End SubI must admit I adapted this code from another example and whilst it works I am not sure about the "found.offset" portion as the original code had different criteria it was searching for
Any help you can give will be most appreciated. Thanking you in advance
Last edited by a moderator:
