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

Pritch558

New Member
Joined
Jan 30, 2015
Messages
18
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.
 

Attachments

  • Capture.JPG
    Capture.JPG
    53.7 KB · Views: 8

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,143
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.

VBA Code:
.....
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

New Member
Joined
Jan 30, 2015
Messages
18
VBA Code:
.....
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 :LOL:

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?:
VBA Code:
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

Well-known Member
Joined
Oct 4, 2007
Messages
1,143
something like this.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,246
Messages
5,595,044
Members
413,963
Latest member
teggl97

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top