Copy and paste certain data from source to destination file

**rizmomin** · 01-13-2015, 05:19 PM

Hello:

Please refer to attached files

I need a VB Code in MAIN.xls to perform the following task.

I have main file attached called "Main.xls", Source files and destination file (DEST.xls).
I am trying to sumarize payroll for employees in weekly (destination) file.

Location of Source file in cell D3
Location of Destination file in cell D4
MAIN.xls will have Start Date and End date in cell A4 and B4.

Example :

I have selected Start date as 1/5/2015 and End Date as 1/7/2015.
Corresponding source file names are in cell A5 and B5 in format (mmddyyyy.csv)
So in this case, basically there are 3 source files which needs to be open in the background.
In each of the source file, Look for text "Z1/Time Keeping" in column A.
Need to copy 3 columns of data from this row till the 1st text "REPORT END" to destination file
Data from 1/5/2015 paste in DEST.xls at cell A1
Data from 1/6/2015 paste in DEST.xls at cell D1
Data from 1/7/2015 paste in Dest.xls at Cell I1
I have attached all of the above files to understand.

Once all of the above is completed save the DEST.xls as file name shown in cell D5 of Main.xls

Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-14-2015, 08:49 PM

Hello Riz,

This workbook will open the source files and copy only the pertinent data to a new workbook. The new workbook will be named and saved using the path and name on the "Sheet1" of the "Main" workbook.

There is a button on "Sheet1" to run the macro. The code is all in Module1.

Please Login or Register  to view this content.

**rizmomin** · 01-14-2015, 09:12 PM

Hi Leith:

Thanks a lot, works...need some tweaking.
I need the files to be copied into DEST.xls as i have uploaded and then save as per name in sheet1 of MAIN.xls.
The reason is as follows:
If you open DEST.xls and go to column AC onward, i have got formulas to separate out employees for the whole week.
I would need to get the unique names of all employees for the week in the grid A1:AA100 and paste in AM2 downward.
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-14-2015, 10:41 PM

Hello Riz,

Here is version 2. This macro expects the destination workbook "DEST" to be open. If not then you will be prompted to open it and try again.

I am assuming you are running an "xlsm" and not an "xls" workbook. A copy of the "DEST" workbook will be saved to the folder with the file name used on "Sheet1" of "Main" with an "xlsm" extension.

Let me know the results.

**rizmomin** · 01-14-2015, 10:59 PM

Hi Leith:
Although i have kept DEST.xlsm open, i am getting error as attached.
Please let me know if you have any questions.
Thanks.

Riz

**rizmomin** · 01-14-2015, 11:06 PM

Hi Leith:

It was my mistake, it works.
As mentioned earlier I wanted to populate the unique names of the employees from cell range A1:AA100 and paste the list in cell AC2 alphabetically.
Basically find all names with Employee # and sort out unique names alphabetically and paste at AC2.

Please let me know if you have any questions.
Thanks.

Riz

**rizmomin** · 01-14-2015, 11:53 PM

Hi Leith:

I am seeing that i will face a problem with my formula approach is cell range AN1:BA20 of DEST.xls file uploaded. The problem will be when employee clocked-In and Clocked-Out more then once in a day.
Would like to ask your expert help how to handle this.
I was thinking to have Data in column AM to AP
AM2 Date
AN2 Employee Name
AO2 ClockIn
AP2 ClockOut
and go down....
Then we can use the data to get hours for each employee for a give date in cell Range AC2:AK20

Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-15-2015, 01:55 AM

Hello Riz,

This version of the workbook adds the employee names starting at cell "A2" on "Sheet1" of "MAIN".

As for your latest question, if can provide a few examples of how you want the output to appear, it will be easier for me to code.

**rizmomin** · 01-15-2015, 04:37 PM

Hi Leith:

Please refer to attached file called DEST1.xlsm.
Two things i like you to know and see if that can be done by VB code.

1. I am manually copying daily time sheet data from Daily Nightly report (attached .csv files) in DEST1.xlsm column A thru AA.
Basically I am pasting each days time sheet data by opening the daily nightly Report file and look for Z1 Time Keeping in column A.
Then i go down until text "REPORT END" in column A. Go across 3 columns, copy and paste in DEST1.xlsm file.
Please help me this automated.
2. Please open DEST1.xlsm file and go across column AM thru AQ.
This is the format i need.

Once this is done, i can use the formula to in column AD thru AJ to give weekly hours worked for all employees.
Remember your uniuqe employee list works and will need to be pasted at AC2.
Also if there is no employee name, then we can have Employee#*.
We can still use the same MAIN.xlsm file

I have attached all files for your referance.

Let me know if you have any questions.
Thanks.

**Leith Ross** · 01-15-2015, 04:46 PM

Hello Riz,

Is there some reason you don't want to use the regular and overtime totals contained in the report? This seems to me like extra work.

**rizmomin** · 01-15-2015, 05:19 PM

Hi Leith:
Do what you can to ease my manual work.
I really appreciate your expert help always.
Please let me know if you have any questions.
Thanks.

Riz
Thanks

**Leith Ross** · 01-16-2015, 02:26 PM

Hello Riz,

I had a bit of trouble that delayed posting this version. I fixed it using a workaround I am not happy with. Since the cause of the problem still eludes me, this will have to suffice.\

Here is what the output looks like using the 3 csv files from post #1.

Great, I can't upload ****!!! You can download it from my MediaFire account. Here are the URLs...

Example Output
MAIN ver 3.xlsm

**rizmomin** · 01-16-2015, 05:32 PM

Hi Leith:

Sorry to bother you but for reason, I tried to download from above URL and started downloading lots of programs.
Please look at the issue and let me know.
Not sure what happened.
Once again sorry to disturb you and thanks a lot for all help.
Riz

**Leith Ross** · 01-16-2015, 06:27 PM

Hello Riz,

I don't know what the problem was. I clicked the links and downloaded each file and no others came along.

The forum uploader is working and I got the file uploaded to my previous post. Try it out and let me know the results.

**rizmomin** · 01-16-2015, 06:53 PM

Hi Leith:
Seems to work.
Don't see any major issue except in column AM have same date first with respective employees in column AN.
Example:
AM2 : 1/5/2015 AN2 ANGELA and clockIn-Out info inAO2,AP2
AM3 : 1/5/2015 AN3 JONES and clockIn out info in AO3 and AP3
and so on.
Do only if this easy for you otherwise i will be ok with what i have.
Would be feasible to have calculated hours worked in column AQ2 but prefer having in fraction for 101/2 hours would be 10.50

Please let me know if you have any questions.
Thanks a lot for your smart help.
Riz

**Leith Ross** · 01-16-2015, 08:13 PM

Hello Riz,

The workbook has been updated to sort the time time by date in ascending order.

Do you want the total hours for a single Clock-In/Clock-Out cycle expressed in half hour increments or in decimal hours?

**rizmomin** · 01-16-2015, 08:36 PM

Hi Leith:

Thanks for fulfilling the 1st requirement.
I would need decimal hours.
For Example

Clock In 18:30 Clock Out 21:45 then total hours worked would be 3.25

Please let me know if you have any questions.
Thanks.

Riz

**rizmomin** · 01-16-2015, 09:14 PM

Hi Leith:

One small request: It would be nice to clear the range A1:AA100 before pasting data in DEST.xlsm
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-16-2015, 09:29 PM

Hello Riz,

This has the decimal hours for each employee's Clock-In/Clock-Out cycle. The macro will hold up to four of these for each employee. If you need more than this, I can adjust the macro.

**rizmomin** · 01-16-2015, 10:10 PM

Hi Leith:

I wonder if you uploaded correct version because this one does not calculate hours and put in column AQ as attached pic
please check and let me know.
Thanks
Riz

**Leith Ross** · 01-17-2015, 02:45 AM

Hello Riz,

Not sure what happen there. It seems to be a day of strange occurrences.

Anyway, this version 3c has all the changes added you asked for and has been tested.

**rizmomin** · 01-17-2015, 08:22 AM

Hi Leith:

Please see attached pic...for some reason ClockIn-Out in column AO and AP are malfunction. Also i do not see calculated hours in AQ.
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-17-2015, 03:57 PM

Hello Riz,

Here is what the output looks like from this version of the "MAIN" workbook. The "Total" column is in decimal hours and formatted for 2 decimal points.

Output Example.png

**rizmomin** · 01-17-2015, 04:57 PM

Hi Leith:

Thanks a lot!!!!!!!!!!!!!!!!!!!!!!!
Now i see it working.
I will test more and let you know if any problems.
Riz

**Leith Ross** · 01-17-2015, 05:16 PM

Hello Riz,

You're welcome.

**rizmomin** · 01-18-2015, 12:42 PM

Hi Leith:

I need a small favor to be added in the code.
As you know that Cell A4 is Week start date and B4 will be Week end date.
While getting the daily Time sheet, if any of the week days file is missing then pop up the message saying Incomplete Days of data, Do you want to continue Yes or No. If no then exit
Please let me know if you have any questions.
Thanks.

Riz

**rizmomin** · 01-18-2015, 12:44 PM

Hi Leith:

I need a small favor to be added in the code.
As you know that Cell A4 is Week start date and B4 will be Week end date.
While getting the daily Time sheet, if any of the week days file is missing then pop up the message saying Incomplete Days of data, Do you want to continue Yes or No. If no then exit
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-18-2015, 08:39 PM

Hello Riz,

The files are now validated against the file dates. If any file name is missing in the date sequence, you will be alerted. The alert will display the folder path and the name of the missing file for that date. You have the option to continue or stop. If you choose to stop, no changes will be made or saved to the destination workbook.

**rizmomin** · 01-18-2015, 11:52 PM

Hi Leith:

Just wanted to see if i could have 4 clock-In out per day instead of 3 as we have right now.
Please let me know if you have any questions.
Thanks.

Riz

**rizmomin** · 01-19-2015, 06:39 AM

Hello Leith:

Please look at th eattached pic since the Main Ver 4 is malfunctioning.
I have choosen the week starting Monday 1/12/2014 and Sunday as ending week 1/18/2015.
As you can missing i have files for 1/12,1/13,1/14 and 1/15 so missing is 1/16,1/17 and 1/18 and hence i shoudl get message for
1/16,1/17 and 1/18.
However i get message starting for missing file for 1/5 thru 1/12.
Please help look into this issue.

Also attached is the pic for ClockIn-Out info for each day for each employee and it is for max of 3 ClockIn-Out per employee.
I wonder if you can allow 4 clockInOuut per employee per day.

Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-19-2015, 04:16 PM

Hello Riz,

The file validation is fixed and the Clock-In/Clock-Out columns have been expanded to four.

**rizmomin** · 01-19-2015, 05:52 PM

Hi Leith:

All works superb except small as seen in pic.
The names needs to be sorted alphabetically in column AC.
1st run it was ok, second run it was as shown in pic.
Please try to fix and Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-19-2015, 11:12 PM

Hello Riz,

This will sort the employees in alphabetical order from A to Z. However, the formulas in the table AC:AK will need to be changed to return the information from the cells in table AM:AW.

If you are unable to do this yourself, you should post a new question for one of the formula gurus or moderators to help you. I have little patience for formulas.

**rizmomin** · 01-20-2015, 08:21 AM

Hi Leith:

please refer to attached pic.
Employee s missing in list column AC.
I think what would be nice to use the employee list in column AN and sort alphabetically and paste in column AC
Please check the code and let me know.
Thanks
Riz

**Leith Ross** · 01-20-2015, 12:10 PM

Hello Riz,

I can not reproduce the error shown. Can you either post the files you are using or email them to me?

**rizmomin** · 01-20-2015, 01:15 PM

Hi Leith:
Sure, please refer to attached file.
Column AC should have all unique employees who clockin-Out during one week period.
Yoy have already done the work to populate all clockin-Out for each day in Column AN.
So basically if AN is used to pick up unique employees and sort alphabeticaly and pasted at column AC.
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-20-2015, 02:15 PM

Hello Riz,

I can't explain your results. Try downloading the attached workbook again from this post and see if the results are different.

Here is the output from the attached workbook using the three original data files you posted.
Output 2015-01-20.png

If there is no change then post the data files you used in your last post.

**rizmomin** · 01-20-2015, 02:53 PM

Hi Leith:
I don't see the problem now...will do more testing.
I think you have cracked it and should not have any problem.
Lastly to conclude this project,
I would like to copy AM2:AN30 of the file name in cell D3 (MAIN.xls) at F2 of the MAIN.xls and
Copy AW2:AW39 of the same file (cell D3) at J2 of MAIN.xls.
This should conclude this project.
I thank you for all the support you provide me.
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-20-2015, 04:52 PM

Hello Riz,

Can you please clarify this statement. I am not clear on what this means...

I would like to copy AM2:AN30 of the file name in cell D3 (MAIN.xls) at F2 of the MAIN.xls

Cell D3 in MAIN is a path to the source CSV file(s).

**rizmomin** · 01-20-2015, 05:29 PM

Hi Leith:

My mistake, Cell D4 is the destination file name and D5 would be the name of the file where to get the data from.
The rest should be clear.
Once again sorry for confusion, if you need more information please let me know.
Thanks
Riz

**rizmomin** · 01-21-2015, 11:05 AM

Hi Leith:

Sorry to bother you but I am trying to add ICount within your coded function below. I wanted a count of how many days of time sheet missing and bring the number in the MAIN.xlsm at cell A21.
I will use this value to warn that so many days data is missing.
Note : I have added ICount before "If Folder"
Please let me know if any questions.
Thanks
Riz

Please Login or Register  to view this content.

**Leith Ross** · 01-21-2015, 02:23 PM

Morning Riz,

This workbook copies the dates, employees, total hours, and missing time sheet count to "Sheet1" of "Main". I have been busy with the insurance company after my wife's car was hit in front of our house. The other driver speed off. No one was injured but the car is in bad shape but still drivable. I won't know the repair cost till Friday.

**rizmomin** · 01-21-2015, 04:05 PM

Hi Leith:

Oh, very sorry to hear about the accident and damage to the car. Important thing is that no one is injured.
I will download the file and test and let you know if any issues/
Thanks a lot and take care of your problem also.
Riz

**rizmomin** · 01-21-2015, 08:02 PM

Hi Leith:

I have tried your code in my own sheet and it is erroring out at : RngEnd at the below code, Can you please hep me locate the problem.
Please let me know if you have any questions.
Thanks.

Riz

Please Login or Register  to view this content.

**Leith Ross** · 01-21-2015, 08:46 PM

Hello Riz,

I have tried to make the code fail but can not. Try quitting Excel and reloading the workbooks. If that does not work then post your workbook.

**rizmomin** · 01-21-2015, 08:56 PM

Hi Leith:

I have tried.
Please find atatched the 2 files..
Please let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-21-2015, 09:36 PM

Hello Riz,

The problem is you saved the file as an xls workbook. The original file is an xlsm workbook. When the code executes the Rows.Count it reads the original number of rows for the xlsm workbook 1048576 rows. An xls workbook only has 65536 rows. Excel confuses itself by reading 1048754 rows and only expects 65536, so it errors.

To fix the problem use the code below...

Please Login or Register  to view this content.

**rizmomin** · 01-21-2015, 10:20 PM

Hi Leith:

Thanks a lot, i will try with more testing and let you know the outcome.
Riz

**rizmomin** · 01-21-2015, 10:37 PM

Hi Leith :

Seems to work great.
I need to take this project one step further.
Now i have data in Column F,G and J, I need to paste this data in Clock-In_Out in the first available blank row staring at Cell A2.
The problem will be to see if any data already exist, if it does then overwrite.
Please refer to attached file.
I have pasted data from column F,G and J manually.
I need a VB Code which will check F2 (Date),G2(Employee) in Column A and Column B in sheet Clock-In-Out, if it exist then overwrite
F2,G2 and J2 at the corresponding in Clock-In-Out. If the data does not exist in Clock-In-Out then write the data in the next available row.
Same with all data in Column F,G and J....
I hope this is clear explanation, however if you have any questions please let me know.
Thanks a lot.
Riz

**Leith Ross** · 01-21-2015, 11:53 PM

Hello Riz,

The last workbook I updated copies all the file information from "DEST" to "MAIN" into columns "F,G, and J". Any information that may be there is deleted before the data is pasted.

**rizmomin** · 01-22-2015, 12:09 AM

Hi Leith:

There is some misunderstanding.
Earlier attached file Test.xls is same MAIN.xls (I have just renamed that for test purpose).
So now, if you look at that file, the data are there in column F, G and J.
This data i need to paste in Clock-In-Out sheet as per my instruction just in my last reply.
Please let me know if any questions.
Thanks
Riz

**Leith Ross** · 01-22-2015, 12:26 AM

Hello Riz,

Are you saying you want to copy the data from "MAIN" columns "J,G, and L" back to "DEST" columns "A and B" if columns "A:B" have the same employees and dates?

Isn't the data from the time sheet files read and copied to columns "A:Z" of "DEST"? If so then why is this data being copied to "MAIN" and back to "DEST"?

**rizmomin** · 01-22-2015, 12:47 AM

Hi Leith:

once again sorry for confusion.
Test.xls is MAIN.xls file except i have changed the layout little bit.
Just to avoid confusion, i am attaching the file Main.
In here every thing is same as original.
We now are bringing data from DEST file into column F,G and J by your code
Now MAIN.xls has another sheet called ClockIn-Out.
I need to paste the data from Import sheet Column F,G and J into Clock-In-Out only if same date Data and Employee matches.
Please read earlier reply about criretia explanation.
Please let me know if you have any questions.
Or if needed i can send you some pics.
Thanks.

Riz

**rizmomin** · 01-22-2015, 12:52 AM

hi leith:
Please refer to attached file.
Let me know if you have any questions.
Thanks.

Riz

**Leith Ross** · 01-22-2015, 01:17 AM

Hello Riz,

Thanks for posting your most recent workbook. Now I see what you talking about.

**rizmomin** · 01-22-2015, 04:56 PM

Hi Leith:

Sorry to bother you, but did you have a chance to look at the sheet and understand.
Please let me know if any questions.
No hurry but wanted to know that you are understanding my project.
I will be using the ClockInOut sheet info for calculating OT and reg Hours which is already working.
So hopefully this is the last step.
Thanks
Riz

**Leith Ross** · 01-23-2015, 05:39 PM

Hello Riz,

I created another module to handle the "Clock-In-Out" worksheet. This will transfer the data from the "Import" worksheet columns "F:G,J" to columns "A:B,E". If there is no data in columns "A:B,E" then the starting row is row 2. Otherwise, the starting row is 1 row below the last row of data.

Add a new module to your VBA project and paste the code below into it. Replace the Sub "ExportTimeSheets" and replace it with the new code that is included.

New Module Code
Name this module "Fill_In_Clock_In_Out"

Please Login or Register  to view this content.

New Code for the Sub "ExportTimesheets"

Please Login or Register  to view this content.

**rizmomin** · 01-23-2015, 06:08 PM

Hi Leith:

It is malfunctioning.
Please refer to attached sheet.
Go to ClockInOut Sheet.
I pressed "Create Weekly Timesheet File" Button to run the macro.
To test i pressed 3 times for week staring 1/12/2015 in cell A20.

Go down row 120 and below of ClockInOut
Each highlighted blocks are the same data.
As you can see they should be overwritten the data in column C of ClockInOut if the date and employee in field column F and G matches with column A and B of ClockInOut.
Please let me know if you have any questions.
Thanks.

Riz

**rizmomin** · 01-24-2015, 04:02 PM

Hi Leith:

I think i have resolve the problem by assigning 1-150 row for each row in ClockInOut sheet and this works ok.
i will do more testing and if any problem i will let you know.
Once again, i thank you for all your time and effort you have spent with this thread.
Riz

Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Re: Copy and paste certain data from source to destination file

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions