Revisions to Macro

**alachape** · 06-23-2008, 03:49 PM

Hi thre, i have the following macro and it is currently working in the attached file. I want to add in some more columns to the sheet "Record Audit" but when I do this messes with the macro.

If I am adding more columns, and shifting the current "L/Scores" column, what part of the macro code do I change so that I dont get errors and that the macro functions as it does now.

Please Login or Register  to view this content.

**Richard Buttrey** · 06-23-2008, 08:01 PM

Hi,

Untested, but presumably everything with a reference to column L. i.e.

Please Login or Register  to view this content.

Change the 'L' reference to whatever your new column is.

HTH

**alachape** · 06-24-2008, 08:40 AM

Hi Richard unfortuantely, changing the L references (from L to M to accoutn for one extra column) does not actually work, it results in a run time error, and the autofilter does not function correctly for some reason and there are no results.

For some strange reason, even though I have changed the references from L to M, it still checks the L column, because if i throw a # in that column for fun, it recognizes and copies and pastes something, not exactly what it should, but it is still checking L and I am not sure why.

Any ideas

**mudraker** · 06-24-2008, 09:09 AM

Try this modification

So you do not have to modify the code everytime you add a coulumn it searches row 4 for the word Scores and sets a variable to that column

Please Login or Register  to view this content.

**alachape** · 06-24-2008, 09:38 AM

Hi Mudraker, thanks for the reply. I really like this idea so I dont have to change the references all the time.

Unfortunately, when I added 3 new columns, and added this macro, i got the same error as before. The Scores column is currently 0

When I run the macro, the autofilter runs, but it has the same error I mentioned before, "No Cells Were Found"

I notice there is a blue "dropdown arrow" beside File 11, in Column L for some reason, which I assume means it is still checking column L for some uinknown reason, probably just to mess with me.

Any ideas?

Oh and if I do the debugger, it highlights this line below:

.Range(Cells(5, iCol).Address, Cells(SLastRow, iCol).Address).SpecialCells(xlCellTypeVisible).Copy _
Destination:=DestSht.Range("E" & DLastRow)

**broro183** · 06-24-2008, 09:44 AM

hi,

Edit, after previewing post: I like Mudraker's technique of checking for the "Score" column & possibly the first sentence of the below which I wrote before seeing the replies is all you need...

**********

I think it should work if the "Field:=12" is also changed to "Field:=13" (ie column M).
Also, I suggest you declare a variable for the letter of the column (or a constant as below):

Please Login or Register  to view this content.

My suggestion for ease of ongoing file addition eg from File 10 to File 11... is to move the four columns L:O (or M:P once a cloumn is inserted) to the left hand side of the spreadsheet & use some sort of dynamic named range to calculate for how-ever many files you end up having.

hth
Rob

**broro183** · 06-24-2008, 09:49 AM

Ooopps!

I forgot to mention that the "evaluate" section will only work for columns A to Z. There is probably a tidier easier way of identifying this - actually, of course there is!

Use the "icol" in Mudraker's code :-)

hth
Rob

**alachape** · 06-24-2008, 09:50 AM

Hi Rob, thanks for the post. I tried changing Field from 12 to 15 since I added 3 columns and it appears to be working. i will do some more testing.

BTW, what is Field:=12 saying?

Thanks

**broro183** · 06-24-2008, 10:01 AM

I'm pleased we could help - thanks for the feedback :-)

To find out what "Field" means, double click on the "AutoFilter" word to select it in the VBE & then press [F1] to open up the Help Files which will tell you among other things that...

Field Optional Variant. The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field one).

hth
Rob

**alachape** · 06-24-2008, 10:50 AM

Hi all, this macro from Mudraker is sweet.

Now my last question, well maybe not the last, but I plan on adding 11 other Record Audit Sheets to this workbook, one for each month, and will rename the files to be January, February, etc.

I want the user to be able to go into each monthly sheet, each month, and then run the macro. The hope is that the Macro in each sheet will keep moving stuff over to the Audit Log for every month.

How do I go about adding the macro to the other worksheets? I was getting some errors about "ambiguous names" when I was trying to use a modified macro on a different worksheet but in the same workbook.

Sorry this may be too all over the place to get what i mean but any thoughts would be great

Thanks in advance

**mudraker** · 06-24-2008, 09:19 PM

If you are running the macro from a button on each sheet you should be able to what you want with 1 macro.
Each button calls/attached to the same macro

You would need to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I'm assuming the destination range code is the same in all the macros you are crerating & is suitable to meet your requirement

Or the 1 macro could be made to loop through all 12 sheets so the user only needs to click on 1 button not 12

PS. Sorry for not picking up the change required in the filter part of the code

Glad to have help you sort out the 1st problem
I assume you followed broro183 advice and changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

to get the macro from erroring

**alachape** · 06-25-2008, 08:49 AM

Hi Mudraker, actually I did not realize the icol change that was needed as I thought it was there already, and i manually changed the field #...I will utilize this recommendation from here on.

Also, thanks to everyone else for your insights and input.

Hopefully I will not be back here too soon haha

Adam

**broro183** · 06-26-2008, 06:47 AM

Thanks for the feedback - I'm pleased we could help

Rob

**alachape** · 07-02-2008, 12:36 PM

Hey guys, i have another question for the experts.

When my macro pastes the scores from my one sheet into the other,
it pastes the cells with the same formatting they had on the previous sheet, for ex: the cells are highlighted in red or orange. One color is the color that the cell is filled in with, for aesthetics only, but the other is a conditional format.

Is there any way I can add something to the end of the macro so that it wipes out the conditonal formatting or other formatting of items pasted into the Audit Log?

Is it possible to only change the format of those things that are pasted or would it have to change format of everythign in the audit log?

Thanks