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
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