Copying data from various cells into new row on another sheet or overwrite if "Unique ID" already exists

[Pritch558]

Hi everyone.

I've searched and searched and can't seem to find what i'm looking for. I'm fairly novice at VBA so not sure where to start so i'll try and explain as best as I can:

In my Workbook I have several sheets. One sheet "Weekly Report" allows the user to select a period and week from drop down menu's in a userform (opened with a command button). Then when they click "OK", a macro then populates the Weekly Report sheet with various bits of information relevant to the period and week selected. Works a treat. What I want to be able to do, is to have another command button in the sheet to "Export" the data.
What I actually want this "export" to do is to add all the data that's just been retrieved and populated in the various cells of the Weekly Report, into a hidden sheet called "Data". But I need the data pasting into 1 row. Then when the report is ran again for a different week, it populates the row below, and so on. Except, it the data has already been populated for that Period and Week in the Data sheet, then it needs to override the data in that row with the new data. Assuming in order to do this it needs to match with a certain criteria that's not going to be duplicated, I've already set a cell in the Weekly Report sheet to populate with a "Unique ID" which consists of the location ID number (set in another sheet) & Period & Week numbers (using the RIGHT function to remove "P" and "Wk" as shown in the screen snip. e.g. if the location ID was 12345 and the report was ran for Period 6 Week 2, cell I5 would read "12345062".

I've attached a snip of what the Weekly Report looks like. As you can see, the cells (currently all "0") where the data is populated are all over the place.
The workbook is protected with password so all VBA in the workbook has to unlock the relevant sheets before locking them again when any changes are made.

Is it possible to do what I want? Or is it too complicated?

Thanks in advance.

 

[rpaulson]

this will find the the row that matches you ID and return the row number (lr)
if the ID does not exist it will return the next blank row for (lr)
you can then use the lr variable to write the record to.

.....
Set rgFound = Range("A:A").Find(ID)
If rgFound Is Nothing Then
 lr = Range("A" & Rows.Count).End(xlUp).Row + 1
Else
lr = rgFound.row
End If
........

 

[Pritch558]

.....
Set rgFound = Range("A:A").Find(ID)
If rgFound Is Nothing Then
lr = Range("A" & Rows.Count).End(xlUp).Row + 1
Else
lr = rgFound.row
End If
........

Thanks rpaulson.
Forgive me, I'm not very good at VBA. Could you "dumb it down" a bit for me

How does this know to match the Unique ID in "Weekly Report" sheet to the unique ID in column A in "Data" sheet?
the .Find(ID) is that a named range?

With regards to using lr to write the record would that be like this?:

Dim Ws As Worksheet
Set Ws = ThisWorkbook.Sheets("Weekly Report")

Cells(lr,1) = Ws.Range("E4")
Cells(lr,2) = Ws.Range("E5")
..... ' and so on?

Apologies, i'm still a noob at VBA, I don't use it all that often

 

[rpaulson]

something like this.

Sub Add_Data()

'I assume you ID file is in coumn "A" of data sheet
Dim ws As Worksheet
Dim lr As Long
Dim rgFound As Range


Set ws = Worksheets("Data")

ID = Range("I5")

Set rgFound = ws.Range("A:A").Find(ID)
If rgFound Is Nothing Then
lr = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
Else
lr = rgFound.Row
End If

ws.Cells(lr, "A") = ID
ws.Cells(lr, "B") = Range("E4")
ws.Cells(lr, "C") = Range("E5")
'.....and so on

End Sub

 

[Pritch558]

something like this.
......

Fantastic. Thanks. I'll give it a try and then mark your answer as the solution.