Need to write a macro to copy a row of information if true [Solved/Closed]

Report
Posts
6
Registration date
Friday December 19, 2014
Status
Member
Last seen
December 22, 2014
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
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.

1 reply

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213 >
Posts
6
Registration date
Friday December 19, 2014
Status
Member
Last seen
December 22, 2014

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.
Posts
6
Registration date
Friday December 19, 2014
Status
Member
Last seen
December 22, 2014

Ok I figured it out. One more question, how do I get the transferr to start on row 3 instead of row 2?
Posts
6
Registration date
Friday December 19, 2014
Status
Member
Last seen
December 22, 2014

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?
Posts
6
Registration date
Friday December 19, 2014
Status
Member
Last seen
December 22, 2014

Thanks it's working just fine.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213 >
Posts
6
Registration date
Friday December 19, 2014
Status
Member
Last seen
December 22, 2014

Hello Irjupiter,

Sorry for the late reply but after I posted my last post, I went to bed! But, anyway, I see that you answered your own questions. Well done!. Please post your resolutions so everyone in the Kioskea community can see the end result. That's what these community forums are for. Help one, help all!

Regards,
vcoolio.