I'm having an issue where I have a macro (see attached file) that is supposed to auto-fill and sort. The problem I'm having is when it tries to run the auto-fill on a sheet in its blank/template state. It will take my formulas in columns AC:AZ and fill them up to the header row.
Please Login or Register to view this content.
This code was hobbled together using record macro and my minor understanding of coding. Please let me know if you need further explanation.
(CK) Macro issue.xlsm
Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.
Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
If you're happy with an answer given, please click the * under the person's name to boost their reputation.
Re: Sorting Macro Replaces Column Headers
I believe this is happening so because when the Macro runs on a blank slate (i.e. with just headers) your last row value evaluates to 3; which is this part of the code; and the Auto-fill then applies to "AC4:AZ3" i.e. Row 4 auto-fills row 3.Please Login or Register to view this content.
To avoid this, you may declare an Interger/Long variable to capture the value of the lastrow; say
. Also, change auto-fill range to "AC4:AZ" & iLastRow.Please Login or Register to view this content.
I did what you said and altered my code (in red) to be
I'm getting an issue where it's stopping onPlease Login or Register to view this content.
What am I doing wrong?Please Login or Register to view this content.
Did you do what jewelsharma told you i.e. add a dim statment for "iLastRow"
Dim as integer works for 32732 row as far as I remember more than that you need to dim it as Long.Please Login or Register to view this content.
Alf
ALF/Jewel,
That seems to be working. I had never made a Dim statement before. But now I have another issue that I probably should have stated in the OP.
My full code (see below)incorporates 4 separate tabs. When I run the code it sets iLastRow to the number of rows in my Q1 sheet only and keeps the value for all the other sheets. I know that a variable can be reset but I don't know how to do it for each individual tab.
Thanks for your help.Please Login or Register to view this content.
The Dim statement is only added once by preference at the start of a macro.
Then you just add the "iLastRow" statement to each sheet you select i.e. for sheet Q2
and similar for the Q3 Q4 sheetsPlease Login or Register to view this content.
An other alternative could be to a "For loop" if you run the same code on every sheet Q1 to Q4
Something along these lines.
As j goes from 1 to 4 sheet Q1 gets activated and code run on sheet Q1.Please Login or Register to view this content.
The command "Next" changes j to 2 an sheet Q2 is then activated and so fort.
Alf
Thanks for the solution(s) ALF. I kept getting an error that said Run-time error '1004': AutoFill method of Range class failed on the lineI had to do some digging around and found that the AutoFill function doesn't work if its autofill range is the same as it's source range (in this case my source range was "AC4:AZ4"). I reworked the For Loop you suggested (So much simpler!) and made it read like thisPlease Login or Register to view this content.
It seems to be working for my needs. I never would have gotten to this point without your help.Please Login or Register to view this content.
Glad to be of help, thanks for feed back and rep.
Alf
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks