Split data into multiple worksheets (and tables) based on criteria in columns

[Carlos Ruano]

Hi, I am completely new to VBA, and am just getting to the point where I am fairly confident with using Excel. I probably have more Excel knowledge than your average Joe, but nowhere near the knowledge of you fine folks

Can you tell me if it is even possible to do what I want? Here is what I have and what I want to do (example file attached):

• I created a template with 35 sheets. Each sheet has a table with only two rows (header row and empty row waiting for data to be pasted).
  • 1st sheet is named "NOH402 - All Results-SI". A1 : P1 contains the column headers
    
  • A2 : P2 are empty cells, but are part of the table ready to accept the source data. Actual column headers are as follows:

Investigator Name, Site Number, Patient ID, Screen ID, Subject Initials, Subject DOB, Collection Date, Visit Name, Accession Number, Department Name, Test Name, Test Result, Units, Normal Flag Value, Test Result Comments, Reference Range.

• All other sheets are named according to "Test Name" (Column K). There are 55 tests, but I am only interested in 34 of them.
  
• For these sheets, the column headers (exactly the same as above) are contained in A4 : P4 (I want the 3 blank rows above the header row).
  
• Additional information: tables in sheets 2 through 34 have additional columns. These consist of one spacer column which will not contain data, and then additional columns with formulas. As it is now, I paste my source data into the first sheet. Then, I sort and paste all rows of one test name into the corresponding sheet and repeat until all sheets have been populated with the source data. The tables expand accordingly per the number of rows that I paste, and the formulas in the right columns do what I need them to do.

So, is it possible to have a macro in my template so that when I open it and paste my source data into the first sheet, that all rows with a particular test name, are pasted into the table on the sheet with the same name as that test name? I am actually working on this for someone else. She is an awesome person, and I want to do whatever I can to make this task as easy as possible for her.

I have found some awesome macros that split data into multiple worksheets based on criteria in columns, but none that fit my requirements for matching test name to sheet name, and allowing 3 blank rows at the top for sheets 2 through 34. Is this even possible? By the way, I am using Excel 2010. Thank you for your time, and any thoughts or feedback would be very much appreciated!

Thank you,
Carlos

 

[vletm]

Before start to thinking more ...
I gotta do two questions!
1) Why do You have many same kind of sheets?
2) How many sheets can You see in one view?

 

[Carlos Ruano]

Before start to thinking more ...
I gotta do two questions!
1) Why do You have many same kind of sheets?
2) How many sheets can You see in one view?

Hmm ... I'm not sure I understand your questions.

I have many sheets that are all the same because when I paste the source data into the first sheet, I want the macro to automatically copy all rows with a single "Test Name", and then paste those rows into the corresponding sheet inside the table. So, for example, I want all rows that contain the test, "Albumin" in column K, to be pasted in the table inside the sheet named "Albumin". Then, I want all rows that contain the test "ALP" in column K, to be pasted in the table inside the sheet named "ALP", and so on and so on ....

As for how many sheets I can see in one view, I really don't understand the question. I can only see one sheet at a time. I paste the source data into the first sheet, and then the macro would do as I have explained above. Then I could review the data for each test individually on it's own sheet.

Please let me know if you need further clarification.

Thank you!

 

[vletm]

If You can see and use one sheet in one time then You'll need only one sheet.
Press [A1]'s [One-By-One]-button to see demo!
It uses this time only 'All Results'-sheet.

 

[Carlos Ruano]

This is very interesting, and thank you so much for your time, but I am not sure I understand what the macro did. I see that it filtered the data one test at a time, but the tables on all of the other sheets are still empty. While this is very neat to me , the goal of the macro should be to paste the rows of each individual test into the corresponding sheet.

I have attached the example file again, but this time with the 2nd, 3rd, and 4th sheets populated. Notice how the test name in the data in sheets 2, 3 and 4 correspond to the sheet name in which they were pasted. I had to do this copy/paste manually. I want the macro to do it for me for all of the tests that have a matching sheet name.

Am I asking too much from Excel and VBA? I have so much to learn!!

 

[Marc L]

Hi !

The easy way at beginner level to copy data from a sheet to another one
is to use a filter or better an advanced filter !

Introduction to Excel Advanced Filters
Extract data using Advanced Filter and VBA …
​

 

[vletm]

@Carlos Ruano
As You wrote ...
a) You can use/see ONLY ONE sheet in ONE TIME!
b) with that 'Demo', You could see that it is possible - no need many sheets!

Next 'reason' to use ONLY ONE sheet is
You will have Your data ONLY ONCE there.
If You copy/paste row from 'all' to 'own sheet' then You'll have Your data in two sheets ... and You might copy again same data again (again) to other sheet.
If You really would like to have data in 'own sheets' then You should MOVE it.
Then You'll have data only in one sheet.

'I have attached the example file again, bu ...' ... maybe coming someday?
... so far it would be challenge to try to any kind of thinking.
And Still... You can see only ONE SHEET in ONE TIME!
... and You'll need only ONE SHEET!
(... and someone has wanted to get 'own sheet' for all rows, ... and someone uses also only one monitor! )

if You really want to ...
then You would use after Filter, something like:
sheets("NOH402 - All Results-SI").usedrange.copy destination:= sheets(.Cells(y, 1)).range("A4")
If Your ... sheet names would be same as used in 'Test Name'
... but those 'Test Name' - ... sheet-names has differences! Then NO work!
... or just need to make a new reference between 'Test Name'-Sheet-names!

>> More useful ... one sheet!

>> You are not asking too much
... many things can do many ways!

 

[Carlos Ruano]

Ok, so then what about all of my formulas on the other 34 sheets? The whole reason that I paste data from the first sheet to all of the other sheets is because I have formulas on all of those sheets for each of the tests. I cannot use the same formula for all of the tests, and there are too many to use a complicated nested IF statement.

Does that make sense? This is part of what I wrote in my original post. I need the data for each test on it's own sheet, because each test has it's own formula.

 

[vletm]

@Carlos Ruano
I quickly check those formulas -
there are maybe 2-4 kind of formulas which has 1-2 'Lundbeck Rerefere-values'
Am I right?
You could have ALL those 'Lundbeck Rerefere-values' in one sheet
and those results can solve without add any formulas in sheet.
More effect to handler those references and formulas.
(btw. Did You want to learn VBA?)
I also noticed that 'Test Result [L]' in 'ALL'-sheet use "." instead "," ...
that makes challenge too if 'just copy & paste' nor use...
but it can solve - not challenge (but it have to take care)!
... and if even one letter is wrong with sheet-name compare to 'Test Name'
then 'manual work'!
Let VBA to do those routines and also let HER to see the results!
>> Still - one sheet - even 'ALL'-sheet and 'references'-sheet

 

[vletm]

... I still offer something like this ...
There are four tests to review ...
It will show 'Results'.
Use only those two buttons [Show] & [Back]!
and it's not ready!

 

[Carlos Ruano]

Ok, thank you for your time. Unfortunately, the design of the deliverable is not up to me. There is a very important person in my company who cannot be argued I have to have the data as they want it. It is ok, I am just about finished with the VBA to do exactly what I wanted. It might not be pretty since I took bits and pieces of code from many sites, but it is working perfectly!

Again, thank you for your time.

 

[vletm]

There are missing only those 'limits'
... many rows same same values.
... and those two graphs were just something extra.
Simon says jump.
And remember then Simon will say:
I want here, between 4th and 5th column one new information ...
It means, that You will do same modification to 35 sheets.
... and next day again.
No need to argue, okay.