Data separation and movement question

This can be done by formulas but you will have to manualy then adjust its outcome: by copy/paste in each sheet and then remove the data from Sheet4.

Oh sure!

The workbook example you shared had some data connections that caused some errors.

Row 1 & 2 on each paste-to tab is actually the criteria the macro is building off of. Make sure your book has that.


To apply it to your own example you will first need to enable the Developer Tab. Go to File, Options, Customize Ribbon, and on the right side under Customize the Ribbon check the box down next to Developer. Hit Okay.

You now have an extra tab at the top of the screen!

Choose Developer, Visual Basic

This will open a new window where you can write VBA code.

At the top click Insert, choose Module

Now, copy and paste this code into that screen

Please Login or Register  to view this content.

There are four chunks of code written here: the first three are copies, the last is the delete.

Wherever you see Sheet4, that is the tab you are pulling from.
The range for Sheet4 comes right after, change this to match all of your data including the headers. (for example, you might change this to A1:J5000)

Zero, 30 Sample, and 50 sample are the places where we are pasting to.


Now let's break down one of the chunks of code:

Sheets("Sheet4").Range("A1:J500").AdvancedFilter _ this is pulling from Sheet4, A1:A500
Action:=xlFilterCopy, _ this is the copy
CriteriaRange:=Sheets("Zero").Range("J1:J2"), _ this is what determines what we copy. it's looking at tab Zero, J1 and J2
CopyToRange:=Sheets("Zero").Range("A3"), _ this is where we are pasting to. cell A3 on Zero
Unique:=False this is part of advanced filter. we are copying all records regardless of duplicates

You will customize each block to match your needs.

The last chunk is the clearing, set that range to match whatever you set the range in the first line.

When you're done with the code, just close that window.

Now, to run the macro you can just choose Macros under the Developer menu and run Filter. Or, you can choose Insert on the Develop Tab, and choose the upper left icon and draw a button somewhere on Sheet4, and then assign that macro to it. Now, when you click the button the macro will go off.

I'm quite the VB novice myself. I can freehand very, very little code.

If you're curious about exploring code, you can record macros by clicking the little spreadsheeet with a red dot in the lower left of your screen and perform some excel actions. When you're done, click the icon again. You can then select that macro in the Macros menu, and choose Edit instead of run. Here you can see all the actual code that was involved in the actions you made.

From there you can slowly begin to understand the code involved by recording very short actions, and dissecting them afterwards.

Here's a beginner's tutorial for creating a few basic macros:

http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

The forum has a list of guides as well:

https://www.excelforum.com/showthread.php?t=823709