Previous Topic Next Topic

Excel VBA

Closed
topinwebhost Posts 6 Registration date Wednesday 20 November 2013 Status Member Last seen 9 December 2013 - 20 Nov 2013 à 20:44
topinwebhost Posts 6 Registration date Wednesday 20 November 2013 Status Member Last seen 9 December 2013 - 9 Dec 2013 à 09:16
Hi,

I have a VBA data entry user form where in 200 people will be updating it and updating different excel sheets and will be saving it. All the data saving to individual excel sheets is working fine. However, I want to build a VBA code which when executed should copy all the data (not fomulas) to on individual excel sheet of all the employees. For example:
Employee A and B are working in 10 to 6 shift. They update the excel workbooks named A and B which has a sheets named sheetA in workbook A and SheetB in workbook B for respective employees saved in different location in a shared drive. When the user opens the userform he will be asked to enter 8 to 9 different fields, which when saved reloads. After updating all their work and when employee A clicks on the log off button, all the data saved on the specific sheetA or and when employee b clicks log off all the data saved on SheetB should be moved to a master sheet and it should be continues and not in different sheets. Assuming A and B have 100 rows updated each at the end of the day master sheet should have total 200 rows updated. And after that the sheetA and SheetB should be cleared for the next days entry. When clearing only the data should be cleared not the formulas. Please help.

Sorry for such a huge explanation, but really looking for this code.
Related:

5 responses

Réponse 1 / 5
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
21 Nov 2013 à 10:32
Hi Topinwebhost,

Well, apparently you know how to put data from userform to specific sheets. Then you will also be able to put that data on the master sheet, right?

To clear the data, figure out which cells you want to clear and use ".clearcontents", to remove data but keep formula's intact.

Let us know where you get stuck.

Best regards,
Trowa

Réponse 2 / 5
topinwebhost Posts 6 Registration date Wednesday 20 November 2013 Status Member Last seen 9 December 2013
21 Nov 2013 à 19:16
Hi Trowa,

Unfortunately, I am not able to move the data from one sheet of the agent work book to the main book sheet. My question is regarding moving the data between the sheet and next day the same sheet in the main work book should be updated again.

That is., the agents have a log off button in the user form, when they click on the button, all the data from MainPRSheet in agents excel sheet should be moved to MainGLSheet. Now, the MainGLSheet should consists of yesterday's data as well and new set of data should be added in the new row. When copy the data from MainPRSheet in Agents workbook the formulas shouldn't be copied.

Please help me with this.

Regards,

Venkat
Réponse 3 / 5
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
26 Nov 2013 à 10:56
Hi Venkat,

Without specifics it will be hard to help you, so consider uploading your file using a filesharing site like www.speedyshare.com or ge.tt.

Take a look at the following code, it might steer you in the right direction: 
Sub RunMe()
Sheets("MainPRSheet").Select
Range("A1").CurrentRegion.Copy
Sheets("MainGLSheet").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Remember to use .ClearContents to remove data from MainPRSheet on cells without formula's to not mess up the sheets structure.

Best regards,
Trowa
Réponse 4 / 5
topinwebhost Posts 6 Registration date Wednesday 20 November 2013 Status Member Last seen 9 December 2013
2 Dec 2013 à 17:48
I have uploaded the Title_production_report.xlsm file http://www.astmng.com/downloads/Title_production_report.xlsm.zip . The username and password to access the above link are. username: astmng and password: changeme@123 . Now, basically, this is a system that allows employees to update their minute wise Call Center production. The steps to be followed are as follows:
1. The employee will only be provided with the option to click on Login Button which is available on the ProductionSheet.
2. When the employee clicks, he/she will be prompted to enter the username and password.
3. When successful, they will start updating their production sheet.
4. All the data that is saved through usrfrm_productionsheet will be updated in "PR" sheet and at the same time, some formulas will be added and further updated in the MainPRSheet.
5. We are looking for a functionality as follows:
- You should be able to see a red colored log off button on the top right corner.
- When the employee clicks on this button.
- The data that they have updated should be copied and pasted to a different workbook named MainGLBook -> Sheet1 and to the first empty cell. And after that the PR sheet in Title_Production_report should be emptied for next days updating.

Note: We will have "N" number of employees clickng on the log off button at the same time. It is true that every employee will have separate work book, but the destination workbook MainGLBook will be same. So, we are looking for a solution wherein the data is not overwritten.

Please help. I'll really be greatful for your help.

Thanks in advance.
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
3 Dec 2013 à 11:42
Sorry topinwebhost, I can't open zip files and could you resave your file with an .xls extention, since I'm still using Excel 2003.
topinwebhost Posts 6 Registration date Wednesday 20 November 2013 Status Member Last seen 9 December 2013
3 Dec 2013 à 13:38
I have uploaded the Title_production_report.xls file http://www.astmng.com/downloads/Title_production_report.xls . The username and password to access the above link are. username: astmng and password: changeme@123
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
5 Dec 2013 à 10:12
Hi topinwebhost,

Note: I didn't need a username or password to open your file.

Your file is pretty empty. Your login button doesn't do anything and I can't find the log off button. If this is a version issue you might be better off starting a new thread asking for help from someone with at least your version of Excel.

You might want to think about how one is to know which info has been updated by a user.

Best regards,
Trowa
topinwebhost Posts 6 Registration date Wednesday 20 November 2013 Status Member Last seen 9 December 2013
5 Dec 2013 à 17:25
Hi Trowa,

I am sorry, but did you try accessing VBA interface. I have many forms there, I have tried accessing the Xls version on 2003 excel installed computer and was able to see the form. Please ignore the button on the sheet. Please go to VBA by pressing Alt+F11 and there you will see usrfrm_loginprompt which is the login interface and the employee report form is usrfrm_productionsheet where you will be able to find the logoff button. Please give it a last try.

If you fail, then I am letting this project go all away as no one seems to be able to help me :(

Thanks.
Réponse 5 / 5
topinwebhost Posts 6 Registration date Wednesday 20 November 2013 Status Member Last seen 9 December 2013
9 Dec 2013 à 09:16
Please any help on this will be great.

Similar discussions

How to invoke VLookUp function in Excel VBA.
Wingie -
TrowaD -

1 response