Retrieving data from another sheet + editing it + re-writing retrieved data

justme101

New Member
Joined
Nov 18, 2017
Messages
34
Hello Experts,

I have been working on a report for my team and have received a lot of help from this forum + the internet :) This particular requirement is a bit tricky for me and I am not able to put together a working VBA code for it. Let me explain the requirement:

1614348944215.png


Look at example above. Assume this is the entry a user has done for today in a sheet named "Entry". After he clocks on the SAVE button, this data gets saved in the "Data" sheet and all data from the Entry sheet is cleared. Code already written for this. You can see there are are two rows with the status as "WIP".

Now, let's say the user comes in to work next day, and wants to see whatever lines contain "WIP", complete the work, and then change the status to "Completed" manually and save it back. The purpose of this is to check when was the work completed.

Requirements:

1. The user can only pull up those lines from the "Data" sheet, which have status as "WIP"
2. He can only change the status on those lines from "WIP" to "Completed"
3. Save it back to the "Data" sheet, which will overwrite the previous lines, which had WIP in them
4. The system date column should pick the system date, duh!, so that the delay to complete the work can be looked at (system date minus req. date)

Thank you, in anticipation.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
Hi,
Its quite possible to do what you want but would be helpful to the forum if you share the code you have to submit from the entry sheet to the data sheet.
Also, a copy of the entry sheet using MrExcel Addin XL2BB - Excel Range to BBCode

Dave
 

justme101

New Member
Joined
Nov 18, 2017
Messages
34
Hi,
Its quite possible to do what you want but would be helpful to the forum if you share the code you have to submit from the entry sheet to the data sheet.
Also, a copy of the entry sheet using MrExcel Addin XL2BB - Excel Range to BBCode

Dave

Hi Dave, Thank you for replying. Here's the code to copy lines from "Entry" sheet to "Data" sheet:

ActiveWorkbook.Worksheets("Entry").Select
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.Offset(0, 20)).Select
Selection.Copy

With Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
.PasteSpecial (xlPasteValues)
End With

Some more info, The entry sheet has headers from A5 to T5, and entry range from A6:T19. I am really sorry, I cannot paste the exact sheet here due to confidentiality issues, I hope you understand.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
Hi Dave, Thank you for replying. Here's the code to copy lines from "Entry" sheet to "Data" sheet:

I cannot paste the exact sheet here due to confidentiality issues, I hope you understand.

Not suggesting post any sensitive data just the spreadsheets you are using as this make its much easier to develop a solution rather than guessing - you can anonymize them if they contain identifying corporate marks

Dave
 

justme101

New Member
Joined
Nov 18, 2017
Messages
34
Not suggesting post any sensitive data just the spreadsheets you are using as this make its much easier to develop a solution rather than guessing - you can anonymize them if they contain identifying corporate marks

Dave
Got your point. However, I found a little workaround for my requirement. I can now pick out the required data (rows of entry) and show it to the user on a new sheet. I, however, have a different problem now, fairly simple but I can't figure it out. Should I post it in a new thread or continue here? Also, can I ask this thread to be closed if I am starting a new thread?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
Hi,
If you have a completely new problem to resolve then should start a new thread - MrExcel does not I think, close threads - You can mark a thread with the accepted solution.

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,466
Members
417,025
Latest member
MusterDuster

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