Copy all but cells that have "Enter Here" text

[chosenp]

Hello,
I am using Excel 2007 with windows vista, but have saved the excel spreadsheet as compatible with 97-2003 because my kids use older versions, and still have windows XP.

I please need help with code that copies data if no entry has been made as indicative by not selecting rows that still say Enter Here text. .

I have created an excel spreadsheet to track peoples birthdays and anniversary dates.
In Row 6 Columns A through E has the following column header.
Enter Name or Anniversary Names / Enter Birthday or Anniversay / Current Age /Age Next
Birthday / Days to Next Birthday or Anniversary

Its set up such that in Col A and B it states “Enter Name or Anniversaries Name” in Col A and “Enter mm/dd/yy” in col B, both have conditional formatting to have a yellow fill and Red bold lettering as long as it states this text. Where by when a new person is entered to track, the formatting goes back to nor color or special font.

After an entry is made in col A and B, besides the conditional formatting change, columns C, D & F change from the word “Value” showing now, and populate based on formulas relating to todays date (which is created in cell A3 using formula =TODAY(). This creates the current age, age at next birthday, and days to next birthday or Anniversary.
I currently have 25 entries in rows 7 through 31 with fixed birthday data, they are grouped by family’s.
Rows 32 through 125 are set up for additional entries if needed, still stating to “Enter Name….etc

Originally I just had all my family listed and I sent a copy to all my kids, and my inlaws to use for tracking.
I added the extra entry rows so the kids could add their inlaws etc.

Then I came up with the idea of creating a macro button that will copy the rows with entries (ie starting with Row 7) and pasting, and sorting them by the “days to next brirthday or anniversary”
I want to paste the sorted data under these additional column headings in Row 6 Col G-K (which is just to the right of the fixed data)

Name /Birthday / Current Age /Age Next Birthday /Days to Next Birthday or Anniversary

So I recorded this macro that starts with deleting any old data that was pasted and sorted.

Sub CopySortBirthdayDates()
'
' CopySortBirthdayDates Macro
' This first deletes previous copied and pasted data, then copies the fixed data in column A-E. Pastes this data in Col G-K. Then sort it by the "days to next birthday or anniversary” in Col K

Range("G7:K165").Select
Selection.Delete Shift:=xlToLeft
Range("A7:E31").Select
Selection.Copy
Range("G7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Birthdates and Anniversarie (2)").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Birthdates and Anniversarie (2)").Sort.SortFields. _
Add Key:=Range("K7:K31"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Birthdates and Anniversarie (2)").Sort
.SetRange Range("G7:K31")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A7").Select
End Sub

This basically copies the rows of data I have and pastes them in Cell G7 Then it sorts all the pasted data based on col K (Days to next birthday or Anniversary.

I want to send this to my kids, they are less literature when it comes to excel formulas and macros than I am. The problem I have and need help with if you guys would be so kind is
1) So when they push the macro I made it will only copy the cell/rows I have entered data for (Row 7-31 or Range("A7:E31") ). I am hoping you can help my macro code to copy the rows that have data in them, ie Rows 7 to 125 ,the ones that do not say “Enter Name or Anniversaries Name” in column A. Such that no mater how my new entries the kids make it will copy,paste and sort all of the ones they have data in.

Please advise.

Sorry to make it so lengthy.
Thanks
Keith

 

[RalphA]

I am not into VBA, but, perhaps the following idea will help you.

assuming your data starts in row 7:
1. In some unused column, say Z, in Z7, enter: =IF(LEFT(A7,5)="Enter",1,0).
2. Do a sort of the range A7:Z125 in ascending order.
3. Do a sort of the range A7:Z??, where ?? represents the last row with a 0 in it.

Does this idea help you?

 

[chosenp]

Good Idea, I should have thought of that. I am using the same Left formula in my sheet now.
I was hoping not to have other data in other cells and I know I can hide the rows. Originally in my first version, I had it set up to refer to other cells to get the data, then I streamlined it realizing I did not need to use all of those cells.
I guess that is why I hoping for some VBA code to be able to copy only the rows with specific data.
Your idea is a great one that i will incoporate until I can determine the vba code to include in the macro.
Thanks for taking the time to help me, I sure appreciate it.
Being over 50 and teaching myself this stuff has been quite an adventure. Its people like you, willing to help others, that makes it all worth while. I hope I can return the favor someday.

Keith