Workbook has a macro that I have no idea how to update. I just want to add columns

So there was an person that was trying to make a tracker for our business group, and unfortunately he left. Basically I want to be able to add columns without the macro "breaking" but I have no earthly idea on how to do that. This macro is way over my head.


I just want to be able to add columns to the "Compliance tracker" tab and still have the macro for sending emails work. Also, if possible when the "Tax return status" is changed, how would I make it so the info in the "Already sent?" column auto deletes?Compliance Tracker - Corporate Draft - final.xlsm

so on the autodelete part of the code, my question is: What is value of column "Tax return status" that you want monitored? Any change or a specific change to something in the drop down list?

for the adding of columns, that is more of just manually inserting a column in case someone wants to add another field that's important to them. I dont want that part to be part of the code if that makes sense.

so a user might add a column name to save particular data but not email that column? what part of the macro will account for the user adding data to the manually created column?

i have the autodelete (cell) working, just need you to verify that you have made the above changes to your code. Does it work, if not please post your whole code so that i can review it.

Originally Posted by dmcgov

so i took a look at your code in module 1. i see where column "T" is hardcoded when it should be the last available column (Cells(1, Columns.Count).End(xlToLeft).Column)

so add this code to your dim statements:

Please Login or Register  to view this content.

then put this line after sendRighAway:

Please Login or Register  to view this content.

now you have to look in your code for anything that has a "T" in it and replace that with icol, like so:

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

one question for you is that when you add a column, you have to program for that column in your code.

as for auto deleting i believe that i have a solution for you but lets get the above working first.

Ok so I input the code you gave me, but I really just need to be able to add columns not just at the end, but in between the columns that already have headers if that makes sense.

well that changes your code then. so here is the issue:

if you add a column past "T" it will grab that. if you insert a column after "D" then your code get's messed up in many places, see this one line:

Please Login or Register  to view this content.

. if you add a column after "D", then "Q" needs to change to "R". You would have to ask one of the guru's how to modify your code to reflect the addition of interior columns. It would make the code far simpler to just add columns at the end of "T".

as for the auto delete, i presume that you mean clear the cell "T" & Target.row?

if so then put this code in the sheet "Compliance Tracker"

Please Login or Register  to view this content.

put this code in a module:

Please Login or Register  to view this content.

read this code carefully as i left a comment on how to delete the row if that is what you really wanted.

if what you intended was to monitor r for a specific change rather than any change, then modify this line:

Please Login or Register  to view this content.

with this line

Please Login or Register  to view this content.

as an example of how to look for a specific change in column R

you might also want to turn the data from Compliance Tracker into a Table, it is easier to manipulate than a range or a region.

Originally Posted by dmcgov

you might also want to turn the data from Compliance Tracker into a Table, it is easier to manipulate than a range or a region.

how exactly would i go about doing that? just selecting all columns and click format as table?

just click in the range and do a CTRL + T. that will create the table for you.

Originally Posted by dmcgov

just click in the range and do a CTRL + T. that will create the table for you.

lol alrighty, well I did that and now the code doesn't work. man this blows.

here i uploaded your file with it working on the "Compliance Tracker". put some data in the column "Already Sent" and then change your dropdown to something.

when you say the code doesn't work, are you talking about your code or my code?

Originally Posted by dmcgov

here i uploaded your file with it working on the "Compliance Tracker". put some data in the column "Already Sent" and then change your dropdown to something.

when you say the code doesn't work, are you talking about your code or my code?

talking about my code.

please explain the steps that you are doing (where the code fails) so that i can walk through your code and make it work with a table.

I took the VBA code in the file you posted and just copy and pasted it in my file. I changed all the "T" to iCol. I changed column R, row 10 to in prep, and went to hit generate email and it says "compile error: variable not defined." I hit ok and it highlights the "Sub generateEmails(). It looks like it might have something to do with the lCol maybe? I attached my spreadsheet with the updated code.

Compliance Tracker - Corporate Draft - updating.xlsm

i must have pasted this wrong somehow,

change this line:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

somehow it pasted as lower case L and not lower case I

let me know if this works now

ok so i changed that. so when i put info in the "Already sent" column and change the status it gives me the box do i want to delete the row/cell. I just want to delete the contents in the already sent cell, and not the row. When I hit ok it says, "Run-time error '1004': Method 'Range' of object '_Global' failed." When i click debug, it highlights this:


macro debug cap.PNG

yup, i see the error. in this case, since the cell that you want to delete is fixed in column "T", then it should reference that. so change the entire "case is" to this:

Please Login or Register  to view this content.

i got rid of the messagebox, no longer needed.

ok so now that is working, but when i try to generate an email it gives me an error. I think it is because I changed all the "T" references to iCol

ok changed the reference back to "T" and now its all working.

Now I just need to figure out how to make the references to all the columns variable, instead of having "T" etc. could you recommend a way to do this? maybe having an input tab for each field? idk just spit balling

well thats cause iCol is equal to 1. and that is because you have blank rows at the top of Compliance Tracker. So delete those blank rows and then add some text to T1 & U1. that way iCol will go to the U column. (or maybe delete Row 1 with the labels in Red, are they necessary)?

then rerun your macro.

Originally Posted by dangerdavedsp

ok changed the reference back to "T" and now its all working.

Now I just need to figure out how to make the references to all the columns variable, instead of having "T" etc. could you recommend a way to do this? maybe having an input tab for each field? idk just spit balling

so since it is now a table, i think that i can just use the Table headers in the name instead of the column letter. i havent done this before so i will have to play around to find out the syntax. btw, i deleted row 1 with the Red Letters. that way the table is clean and the headers are on row1. hopefully should be done in an hour or so. if not, i will play around with this after work.

the blank row (A1) will eventually have a company name in it. I removed it for privacy reasons. I would like to keep the red lettering in row 7 since this item will be used by a lot of people. ease of use is why they are there.

with the table headers idea, i wonder if we have an input tab for headers specifically. then next to them we can have what type of function it is serving.

header 1 = CorpTax ID - used in email
header 2 = Form - informational only - not included in generated email

does that make sense?

Originally Posted by dangerdavedsp

with the table headers idea, i wonder if we have an input tab for headers specifically. then next to them we can have what type of function it is serving.

header 1 = CorpTax ID - used in email
header 2 = Form - informational only - not included in generated email

does that make sense?

i'll come back to this (i have some questions) once i have gotten the column names to work correctly.

i dont think it's possible when using a table, but we could have a header tab that is just used for the VBA and then it goes and looks for that header in the compliance tracker tab

any luck over the break? thanks again for helping me!

so i will go though each column letter in your code and then make new code. use this as a template for your code.

so change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

still working on the select case statement, not sure if i can do this. going to ask alphafrog to comment on this.

Originally Posted by dmcgov

so i will go though each column letter in your code and then make new code. use this as a template for your code.

so change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

still working on the select case statement, not sure if i can do this. going to ask alphafrog to comment on this.

you are really amazing. I really really appreciate all your help!

I just bought a tutoring program for this stuff, so hopefully that will help me.

Originally Posted by dmcgov

so i will go though each column letter in your code and then make new code. use this as a template for your code.

so change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

still working on the select case statement, not sure if i can do this. going to ask alphafrog to comment on this.

any luck with alphafrog?

thank you so much again for helping with this.

Originally Posted by dmcgov

so i will go though each column letter in your code and then make new code. use this as a template for your code.

so change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

still working on the select case statement, not sure if i can do this. going to ask alphafrog to comment on this.

Any luck over the weekend? Thanks!!!

@dangerdavedsp

i think that it is best if you open up a new post for this. Title it something like this "How to make my code work with table column names". post the code that you currently have. i will again ask alphafrog to take a look at this.

best of luck

dan