Collecting Data from one WB to another WB and then Final values arriving - Time & Pay

I'm sorry, I'm a little confused as to what you are trying to do. Could you please reload sample workbooks with the correct names that you've used above? You are referencing workbook ending in P2 and none of your workbooks loaded have that name. Also, in your workbook could you do two worksheets, one what it looks like before the macro and the other a copy of the worksheet, updated to mock what you would like it to look like after the macro.

Thank you,

RVASQUEZ

P2 = Part 2 (16 to 31st) which is July2012P2.xls

I have uploaded the correct file (july2012P2.xls) which has sample data.
I will receive a data loaded file named july2012P2.xls with the data from A : R, then
Step 1 : With the help of vba/macro formula i need get the result from S:Z
Step 2 : Get this result to AB.xlsm WorkSheet : Jul_P2 (to Row 25 to till down) Consolidated Name & Code wise
Step 3 : From that consolidated result i need to get total R Hrs, Extra Hrs, Total Pay for a particular employee from (16 to 31st July)

Thank you so much for your time

SK

oh.. excited

1. YES - Worksheet always named ComPayPunch
2. If i understood your question, Already sheets must be created from Jan to Dec each sheet will have a button to get the result for that particular period. Please revise your question if my answer is not relevant.

Thanks for your effort

SK

Okay so this is what I have so far. Can you take a look read through it and let me know if it will work?

When you open the workbook, enable macros and also Press Atl+F8 and select the copyToAB macro and select Step Into. There is a comment that states PLEASE NOTE. Please change the filepath before attempting to run the code as it will not work on your computer as is.

Right now the macro formats the data in the July2012P2_Sample.

It begins by performing step one, you requested. It then copies the comPayPunch worksheet to the end of the workbook. It then proceeds to format the data so that it look like you had defined in Step2.
Next it copies the formatted data on the inserted worksheet, opens the AB workbook and looks for the month and either the first or Second run depending on the date. It then pastes the copied data to row 25 and begin step 3 of assigning formulas to row 4 through 24.

Finally it closes the AB workbook, deletes the inserted worksheet from the Sample workbook and ends.

Hi rvasquez,

As guided I have changed the file path. and clicked the button at Jul2012P2_Sample(1).xlsm
and studied the action.

All the calculation is great effort and thanks a lot.

It works Great, But completely reverse. Im worried that my explanation is not good.

An auto generated file will not have this type of genius coding inside. May be we have to take this coding to AB.xlsm

---
Step 0 :
a : Assuming that we have a auto generated set of excel file named already like Jul2012P1.xls, Jul2012P2.xls, Jun2012P1.xls, Jun2012P2.xls, Feb2012P1.xls ... so on, upcoming month will auto generate from the timer machine to this folder - All files will have only data up to (A : P)
Coding cannot be inserted each time by the users. So... We have to Make AB.xlsm is the Main File with the coding.

b: AB.xlsm : will contain WorkSheet (WS) Start Page, Master, Jan_P1, Jan_P2, Feb_P1, Feb_P2,........., Jul_P1, Jul_P2......, Dec_P1, Dec_P2
AB.xlsm will also contain all the necessary coding to get the result from the relevant month (stated below Note1)

Inside the month WS:Jul_P1,
- Pre-filled data will be occupied from A2: Z23(Employee Name , Code, No of Days Present, Total R Hrs, Extra Hrs, Total Pay )
- Save Button - For saving the current period into new workbook with the same name has the work sheet name

Main file is AB.xlsm, from here the users clicks the button and then the action takes process

Step 1 :
1. We first open the Main file "AB.xlsm" > Start Page >
2. Selecting the period(Note1) It will give, from which file we have to get the data.

Note1:
User selects the file to pick the result WB: AB.xlsm > WS: Start Page > (A2) YEAR , (B2) Month (B2), and (C2) Part = (E2) eg: C:/......Jul2012P2.xls(m)

3. Then we should have a button on the Start Page which will Trigger the action > Open the exact file
4. Calculation takes place (which is happening now)

Step 2
1. Receive that calculated data and consolidate them into AB.xlsm > Relevant Month eg: WS(Jul2012_P2)> to A25 to Till required

Step 3
1. Finally the required result we be collected to top pre-fixed table(A2:Z23) with respect of each employee wise.
2. Clicking the Save Button will leads to save the particular sheet for future reference.

Sorry if I am again confusing, If you want i can explain more...

Thank you so much


note2: While calculating, some data are "######" how to avoid this. ?!

Okay just so I make sure I understand. What I gave you looks fine, you just need it to do it starting from AB?

Yes

Thank you!

Alrighty, lets try this one. This one the user selects the year, month and part on the worksheet StartPage in the workbook AB. Then the user selects the Run Report button. This button will open up the workbook whose filepath is located in cell E5 of the worksheet StartPage. (You'll need to change the beginning of the filepath to your filepath again) It then formats the opened workbook and copies the information to the appropriate than in the AB worksheet. The macro then closes the opened workbook without saving changes. Finally, as requested, the code changes any negative numbers that are shown as ###### to 0 and returns to the StartPage worksheet and provides the user with a message box that the data has been imported and the worksheet it was imported to.

Let me know if this works!

Thanks!

Hello there,

I'm not sure if this accomplished what you were trying to do but it's been awhile so I'm going to unsubscribe from the thread. If you need furher assistance feel free to inbox me with a link to this thread.

Thanks!

Alrighty, in order for the code to copy the wbAB worksheet into the wbMonth workbook you'll need to save the wbMonth workbook as a xlsm file. The code does not automatically do this for you so you will need to do this before running the code. Then you can use the attached workbook AB with an updated code.

Please note:

The file path extension in cell E5 has changed from .xls to .xlsm

Thanks!

Yes! that was perfect has planned.

1. Can we also keep the calculation happened in ws ComPayPunch
2. Is it possible of displaying Emp Code wise? instead of alphabetical order.

Thank you so much.

Hello there,

As much as I love to help you I would also like if you could attempt to alter the code yourself.

For Step 1.

You can remove 3 lines of code from the copyToMONTH macro to get these results

For Step 2.

In the macro CopyOriginalDataToNewWs find the part of the macro that sorts the selection. And instead of sorting Key1 by .columns(3) sort by .columns(2)

Let me know if you can't adjust the code by yourself and I'll help but I think it's good for you to learn how to read the code just in case problems arise in the future as not everyone has the same thought process when writing code.

Thanks!