I have limited experienced of writing macros, and I'm looking to update a current spreadsheet used at work. Currently we copy the entire Master worksheet and paste it into other worksheets before sorting for the "X" in certain columns to delete other rows on the master worksheet.
What I am looking to do is search the Master Sheet, and if Column B has an "X" then copy the entire row and paste it into a worksheet named "Column B". Then, once Column B was completed and pasted, it would look at Column D. If Column D had an "X", it would copy the entire row and paste it in worksheet tab named "Column D".
Thanks in advance!
Best Answer
Approach
I should have included this in the first version of my answer.
My solution depends on AutoFilter. I first offer a play solution that demonstrates this approach by:
If this approach appeals, I refer you to my answer to another question which creates a menu so the user can select which filter they want.
If this approach does not appeal, I offer a second solution which involves copying the visible rows left by each filter to other worksheets.
Introduction
You say "I have limited experienced of writing macros" which I take to mean you have some experience. I hope I have the level of explanations correct. Come back with questions if necessary.
I assume your workbook is on a server. I assume someone has write access to update the master worksheet while others open read-only copies so they can look at the subsets of interest to them. If my assumptions are about right, take a copy of the workbook for you to play with. Don't worry about others updating the master version of the workbook, we will copy the final version of the code from your play version when we have finished.
Step 1
Copy the first block of code to a module within the play version. Near the bottom you will find
Const WShtMastName As String = "SubSheetSrc"
. Replace SubSheetSrc by the name of your master worksheet.Note: the macros within this block are named
CtrlCreateSubSheetB
andCreateSubSheetB
because they are play versions. The real versions are namedCtrlCreateSubSheet
andCreateSubSheet
.Run macro
CtrlCreateSubSheetB
. You will see the Master worksheet but only those rows with an "X" in column B. Click on the message box.You will see the Master worksheet but only those rows with an "X" in column D. Click on the message box and the filter will disappear. Switch to the VB Editor if you are not already there. In the Immediate Window (ClickCtrl
+G
if it is not visible) and you will see something like:Now work down macros
CtrlCreateSubSheetB
andCreateSubSheetB
. You must understand how these macro have created the effects you saw. If necessary use VB Help, the Debugger andF8
to step down the macros to identify what each statement is doing. I believe I have given you enough information but come back with questions if necessary.Step 2
If my assumptions about how you use the workbook are correct you may not need much more. If John and Mary each open a read-open copy of the master workbook then John could use the B filter while Mary uses the D filter. If this sounds interesting, look at my answer to copy row data from one sheet to one or more sheets based on values in other cells.
Step 3
If you do not like the idea of just using filters and still want to create copies of the B data and the D data, you will need the code below.
The macros within this block are named
CtrlCreateSubSheet
andCreateSubSheet
but are not much different from the B versions above.In
CtrlCreateSubSheet
you will need to replace "SubSheetSrc", "SubSheetB" and "SubSheetD" with your names for these worksheets. Add further calls ofCreateSubSheet
for any further control columns.Note: these version delete the original contents of the destination sheets although this is not what you have asked for. I have deleted the original contents because (1) what you have adding new rows is more complicated and (2) I do not believe you are correct. If there is some significance to what you requested then come back and I will update the code.
Step 4
Once you have deleveloped the macros to your satisfaction, you will need to copy the module containing the macros from your play version to the master version. You can export the module and then import it but I think the following is easier:
You will need to teach whoever is responsible for updating the master version to run the macros whenever a significant update is complete. You could use a shortcut key or add the macro to the toolbar to make the macro easy to use.
Summary
Hope all that makes sense. Do ask questions if necessary.