10 replies
by ffrescue724 Accepted solution
by deadly ernest Accepted solution
by thumbsup2 Accepted solution
... isn't it amazing what we've forgotten? ;)
and... I have to go back to work (home for lunch) and can't take the time to go play with things right now to give them specific instructions. :(
by thumbsup2 Accepted solution
You could use a complicated IF formula in combination with VLookup in all of the cells on sheet 2, but the output would have blank lines in it for those who have not contributed. That might be OK if you wanted to then sort the data down to eliminate the blank lines, but you would have to copy the entire thing and paste special (values) to get rid of the formulas.
What you could do though would be to create a MACRO which uses VBA programming to search sheet 1 for lines with an amount in a certain column, then copy the corresponding records to sheet 2.
Let us know which you would prefer and one of us should be able to come up with something for you.
by ffrescue724 Accepted solution
Right now I am looking to keep it as simple as possible. So if the macro is something that can be done with out my pulling what is left of my hair out I would appreciate it!
I am a novice Excel user so right off the bat marcos are a bit ahead of me.
Also blank spaces would then keep the secont sheet very large and not a user friendly sheet.
by tmalo627 Accepted solution
From what I'm understanding, you need to be able to keep records of who donated and how much. You also want to thank those that donated, but not be so specific as to mention the amount.
If this is your overall goal, I don't think you even need two sheets. All of your info can be stored on the one sheet. When you do your mail merger through Word for your address labels, you can customize which columns to include on the label. Uncheck the box marking the "Amount donated" field, and run the mail merge like normal.
I hope this helps.
by thumbsup2 Accepted solution
... to at least filter the records prior to the mail merge, since they don't want to send letters to people who did not donate (amount=0).
I believe that was the intention of copying JUST those records to a 2nd sheet.
by ffrescue724 Accepted solution
by deadly ernest Accepted solution
you have a column for donations this year, and then just add another column for future years as they arrive. Have all the donation columns formatted to a money format with a default entry of 0 - zero dollars.
When it comes time to send out the thank yous you just sit down and run a mail merge or a print request utilising a filter on the column for the year you're interested in. When printing, set the filter of >0 that means greater than zero, and all those with a zero entry will not be shown and the report compressed as that line is eliminated, the rest print out in the print report format wanted. End of story and usable in future years too.
by dmvbuyer Accepted solution
Hello,
A simple macro can do this. Basically, the macro will take your data, copy it to another book (it will create its own book), and then delete all the specified data.
Here is the macro.
Sub delete()
'
' delete Macro
' Macro recorded 8/18/2009 by DMV
'
'
Sheets("data").Select
Sheets("data").Copy
Range("a1").Select
Range("B4:B65000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.delete
End Sub
Explanation:
Ok, first thing to do when testing out suggestions like this is to save a copy of your workbook in case something goes wrong (a back-up).
Second, open your workbook and rename the sheet with all your data, "data."
Third, create a button
Fourth, right click on the button and click "assign macro."
Fifth, in the pop-up screen, click "new," which will open the VBA Window. Next, paste this macro into the window. Once your done with this, simply exit out of the VBA window
Sixth, right click on the button again, and click "assign macro."
Seventh, click the macro that is titled, "delete," and click ok.
Eighth, go back into the VBA window and make ONE modification to the macro. The line that reads
Range("B2:B65000").Select
changer the "start-row reference" (the "B2") to whatever column and row number is the donation amount. In other words, if you have a header row, and your data starts on row two, and if your donation column is column "F," then you would replace "B2" with "F2." Also, change the "B65000" to "F65000" (assuming F is the column your adding your donation amounts into.
What will happen when you click your macro button is this: all your data is going to get copied into a new book. Once that is done, the macro will look for every row in column "F" that is blank (meaning, they didnt donate). Every row with a blank entry under "F" will be deleted, leaving you with just the people who donated money.
Save this new book as the year in question, and voila, you have a functioning book ready for mail merging.
Hope that helps,
Jason
This discussion is closed to new replies.
Trending Hardware posts
Sort by Trending