Need to write a macro to copy a row of information if true

Solved/Closed
lrjupiter Posts 6 Registration date Friday December 19, 2014 Status Member Last seen December 22, 2014 - Dec 19, 2014 at 01:26 PM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Dec 22, 2014 at 06:42 PM
Hi,
I have a workbook with several tabs and I want to first check if Column F on the first sheet contains 13 as the year (12/16/13 is the format) and if yes then copy the entire row to sheet number 2 entitled Projects 2013. And then check all rows on the first sheet Column F and copy to sheet number 2 if has 2013 as the date. Once I get this to work then I will do a sheet 3 for 2014, etc.

Please HELP.
Related:

1 reply

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Dec 20, 2014 at 11:19 AM
Hello Irjupiter,

I'm not sure how your workbook is set out but have a look at the following link:-

https://www.dropbox.com/s/3p65onfgjr68r7a/IrJupiter.xlsm?dl=0

I have created a work book with five sheets with tab names for the years 2013, 2014, 2015, 2016 with the first sheet named "Projects" just to see if its what you had in mind. The first sheet I named "Projects" as I assumed that this would be your Master sheet with all your project data going into this sheet and then dispersed to the other sheets by year. The dates in the "Projects" sheet are all mixed up just as a test for the code. I also assumed that there are older project details in the "Projects" sheet that you no longer have any interest in and which will stay behind once the other project details are transferred to their respective sheets. So for this purpose, I have included a "Sort" button which will tidy up the "left overs" for you until you decide what to do with them. Click on the "Transfer Data" button first, then the "Sort" button.

I hope this is what you had in mind.

Kind regards,
vcoolio.
0
lrjupiter Posts 6 Registration date Friday December 19, 2014 Status Member Last seen December 22, 2014
Dec 22, 2014 at 08:21 AM
Thanks but I wanted to copy the data to the other five sheets without removing it from the first sheet because I have some statistics that are calculated from the first sheet. How can I see the formulas or macro details from your "transfer button"?
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252 > lrjupiter Posts 6 Registration date Friday December 19, 2014 Status Member Last seen December 22, 2014
Dec 22, 2014 at 09:09 AM
Hello Irjupiter,

Once you download the DropBox link, go to the Developer tab in the ribbon. Over on the far left, click on Visual Basic. Here, the code field opens. On the far left, double click on Module1. In the large white field that then opens, you will see the code. In the code, wherever you see the line:-

rng.EntireRow.ClearContents

just delete it (there should be four such lines of code). Sheet1 details will then be left as is. Be aware, though, that if you remove the above line of code, the transferred entries will duplicate each time you transfer data. However, you can remove duplicates simply by going to the Data tab, select the Data Tools group and select "Remove Duplicates". You will need to highlight the whole range of data so just drag your cursor across the letters at the top of the sheet which will highlight the entire columns you wish to deal with. In the dialogue box that opens, leave all the boxes ticked and click OK.

The "sort" code is in module2. This still may come in handy for you so just leave it as it is and use it if you choose to.

Just copy the amended code to your actual work book when you are ready to do so but play with it first in the link just to make sure its what you are after. Remember to change all sheet names in the code to match exactly to your tab names.

Regards,
vcoolio.
0
lrjupiter Posts 6 Registration date Friday December 19, 2014 Status Member Last seen December 22, 2014
Dec 22, 2014 at 09:00 AM
Ok I figured it out. One more question, how do I get the transferr to start on row 3 instead of row 2?
0
lrjupiter Posts 6 Registration date Friday December 19, 2014 Status Member Last seen December 22, 2014
Dec 22, 2014 at 09:52 AM
Ok I figured out my second question also. 3rd question, my first sheet has headings on the first 2 rows and when I run the macro it pushes my data up one row on the first sheet... why is that happening?
0
lrjupiter Posts 6 Registration date Friday December 19, 2014 Status Member Last seen December 22, 2014
Dec 22, 2014 at 10:35 AM
Thanks it's working just fine.
0