How to Random Selection of Rows of multiple Workbooks

June 30, 2015 at 09:06:42
Specs: Windows 7
Hi All,
First of all I must say that I am a beginner to Macros.

I have multiple workbooks in a folder and each workbooks has 5-6 sheets based on transaction( .i.e Sheet 1 has Recon, Sheet 2 has follow up, Sheet3 has Initial Check etc.) Each workbook belongs to each of my team members. Everyday each member updates their work in their respective workbook.

I need to select RANDOM 10% of TOTAL TRANSACTION done by EACH MEMBER based on CURRENT or YESTERDAYS DATE( Trade Date) and want to Copy the data to a new Workbook based on Date. I know its a hard-gut job but please help me to reach atleast near !!


See More: How to Random Selection of Rows of multiple Workbooks

Report •

#1
June 30, 2015 at 12:11:30
Without detailed information related to each worksheet's layout, it would be impossible to write any code that would just drop in and work. As you may know, macros are not like formulas where sometimes all it takes is a simple change to the referenced cells for a "generic" formula to work. Macros are very specific and need to be told exactly where to get the information from and where to put it.

This best I can offer at this point is to direct you to the following thread, where a user asked for a macro that would copy/paste a random 20% of rows from one sheet to another (sound familiar?). While the method to accomplish the overall goal can be found within in the code, it obviously isn't going to work as-is for you because your sheet layout is different.

http://www.computing.net/answers/of...

Let us know if that thread at least gets you started.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 1, 2015 at 09:09:04
Hi Derby,

Thank you very much for the advise.Can you please specify what further details you would require - I'll be happy to share my inputs the best way I can.

My View -- - -

A folder contains 9 workbooks.Each workbook contains 5 sheets. Everyday a person updates his work/ inputs in his respective Workbook. I need to select Random 10% of yesterdays work done by each person.

So i think it goes like this:-

Random 10 % of yesterdays work done by each individual in each tab/sheet AND copy those 10% data to a new workbook.

Awaiting for your comments ! :-)


Report •

#3
July 1, 2015 at 10:14:12
re: "Can you please specify what further details you would require - I'll be happy to share my inputs the best way I can."

At a minimum the code would need to know how to determine what is considered "yesterday's work". i.e. The code would need to know where to find a group of dates or something that designates each piece of "yesterday's work" and then count how many entries meet that criteria and then calculate 10% of that number.

The code would need to know when "yesterday" was. If today is Monday, is "yesterday" Sunday or Saturday or Friday. If today is December 26, is "yesterday" Christmas Day or Christmas Eve or the day before Christmas Eve? If "yesterday" is always the day preceding the current day, that's easy. If the code needs to calculate when "yesterday" is based on weekends and/or holidays, then things get considerably more complicated.

The code would need to know what to copy. Is each piece of "yesterday's work" a single row? If so, it is the entire row? If not, exactly what range does need to be copied and how will the code know what makes up that range for each transaction? - e.g. X number of Rows or the data in between the row that starts with xxx and ends with yyy, etc.

When the data is copied to the new workbook will it just be a list of 10% of everyone's "yesterday's work" with no worker-identification data attached or do the groupings need to contain some information related to whose work it is? i.e. If the work needs to designated as "10% of Robin's work", will that information already be part of the copied data or does the code need to keep track of whose workbook/which worksheet the data was copied from?

The best way for you to tell me what the code will need to know is to think about how you, as a user, would do this manually. How would a user determine what data to copy? Would the user look in Column A for a date and copy each row with that date? If each day's individual transaction data contains multiple rows, how would the user know when to start and stop selecting data in order to do the copy? If the data needs to be identified as "Robin's work" once it is pasted into the new workbook, what would the user do to add that identification marker?

There is probably a number of things that I didn't think of to ask because I don't know your layout or process. If there is anything you think we need to know, be sure to include it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 13, 2015 at 09:58:31
Hi Derby,

Sorry I was out of town and couldn't get along with you.
Ok , listed down are the criteria and ways how i need the rows copied.

1. "Yesterday" would be "trade date" i.e Mon - Fri. It is ok if we come across any holidays.

2. Each piece of "yesterday's work" work would consist of an entire row ex. A2 to K2. and each piece of work would have a date updated to it.

3.Range should be always the data of "yesterdays work".

4. Each member's work is identified in the sheet under CW (May be Column K or L ) for each sheet and work book. that if I have done the work, my name will be under the column K or L or any other column. We dont have to track it as you asked because it is already in the tracker.

Now, I will describe how we do it manually. May be you can get an idea from it apart from the above.

We have a folder named for example Global. There are 5 Workbooks in it for tracking each members' daily process/work. Now what I do is I create a new excel workbook in desktop with the same format as in the folder.Lets say it is the Master QC tracker. Now I open any of the Workbook (say Mine) in the folder.I have 5 sheets inside the workbook viz Rec, Fup, IC, IC1 and IC2. Yesterday I might have done 60 Rec, 9 Fup, 20 IC, 30 iC1, and 30 IC2.

I filter by yesterdays date.

copy 10% of each transaction from yesterdays work i.e 6 Rec, 1 FUP ( Atleast 1 if total fup is below 10 ), 2 IC, 3 IC1 and 3 IC2.

Paste the data one by one to the respective sheets of the workbook in the desktop..

message edited by RobinMicheal


Report •

#5
July 13, 2015 at 10:05:46
Then I close my workbook and go for another one which is inside the folder.

Open it. Copy 10% of rec ( if 50 then I manually select random 5 out of it ) and paste it in Master QC's Rec sheet.

Select FUP sheet, copy 10% ( if 19 then I manually select random 2 out of it) and paste in Master QC's FUP Sheet.

This process goes until every member's data are collected from their sheets.

I forgot to mention one thing. Some tracker may have datas from A2 to K2 and some might have data from A2 to H2. so we need to select the last cell with data.

Please do let me know if the above was helpful. I tried to send a sample of our workbook. But I couldn't find a way for uploading it.

Please advise if any further clarification is required from my end.

message edited by RobinMicheal


Report •

#6
July 13, 2015 at 11:19:02
re: "2. Each piece of "yesterday's work" work would consist of an entire row ex. A2 to K2..."

A2:K2 is not an entire row. A2:XFD2 is an entire row.

re: "I forgot to mention one thing. Some tracker may have datas from A2 to K2 and some might have data from A2 to H2. so we need to select the last cell with data."

Do we? If there is no data after H2 (or K2) in both the source worksheet and destination worksheet, then the entire row can be copied. Since you mentioned that the "last cell with data" needs to be selected, that leads me to think that we can simply copy the entire row, which is more efficient.

re: " I tried to send a sample of our workbook. But I couldn't find a way for uploading it."

You can upload the file to a file sharing site such as zippyshare and then post the link back here. Just avoid using a site that forces us to register because odds are we won't go through that trouble. The easier you make it for us "free" volunteers, the better.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question