Copying cells if condition is met [Closed]

Report
Posts
2
Registration date
Wednesday May 24, 2017
Status
Member
Last seen
May 26, 2017
-
 Blocked Profile -
Hi everybody,

I'm trying to make a spreadsheet with a "master sheet" with all the information for the people registered in our club. In this master sheet, I have one column (A) with the first names of the registered, another column (C) with the last names, and columns E, F, G, H, and I, which are labelled Weeks 1, 2, 3, 4, and 5, respectively. In each column E-I, I have input Y or N (yes or no) to indicate whether the person is registered in the club that week.

I have 5 separate sheets labelled Week 1, 2, 3, 4, and 5, and I have been trying to create a system in which I simply indicate whether a person is enrolled for a specific week on the master sheet using the text string Y or N, and it will autoupdate on the weekly sheets, and it will write the person's name (combining column A and C), age (column J), and phone number (column N) on that same sheet in separate columns.

This is what I've made so far for Weekly sheet 1:
Column 1: Name
=IF(Master!E5="Y", Master!A5&" "&Master!C5,"")

This code looks at the value for "week 1" for each row, and if that row has "Y", it takes the first name, adds a space, and adds the last name so we have the full name.
Column 2: Age
=IF(D5<>"", Master!J5,"")

This code checks if the name cell to the left (D5) is empty. If the cell is not empty, it finds the corresponding age value in the master sheet.
And I've done something similar for phone number.

I don't know macros that well, but I think I'll need to use a macro to make it autoupdate and remove spaces. My code above works, but it leaves spaces if a row doesn't meet the criteria, and isn't quite as pretty. Could someone please help me out? I'd be appreciative of any pointers.
I've found some code that is similar to what I'm doing <REMOVED BY MODERATOR>, but it doesn't explain what each line does and so I can't adjust it effectively.

1 reply

Where does it leave spaces at? If you have a loop, that checks for a blank, and keep track of what row it is happening, the to highlight the row and delete it is easy (if that is what is happening)?

ActiveSheet.Range("A1").EntireRow.Select


Selection.Delete


You code is probably a cut and paste from the other site, but you need to paste it here so we can look at what we are actually dealing with. I am not prepared to spend time to have you come back and say it doesn't work because you failed to mention that you use OLE or some stuff!

Post bask with some code, or at least let us know if we give you the code to delete a whole row, and you are keeping track of it all, would work!

Have FUN!

It's kind of fun to do the impossible! -Walter Elias Disney