Need Help with Macro, filtering one column, and filldown value to last row of a new column

[mDevel]

This is my first time creating a Macro, so I apologize if I sound newbish.

I'm creating a macro that allows me to filter out certain values in a column.
I would then have to insert a new column, and enter either Yes or No in that same column, and I would like it to fill all the way down to the last row of that column (and the number of rows changes upon utilization of another worksheet)
I would have to repeat this step for the next 2 columns that I need to filter out.

So for instance, Filter column R and deselect some of the values. Insert a new columnin column B. Type "No" in that column and have it fill to the last row of the column.

My problem is, when I'm opening another worksheet to test to see if it works, the column with either Yes or No, follows the same pattern as the the worksheet where I've created the macro itself. Here is my code:

Please Login or Register  to view this content.

Please help.

[judgeh59]

Howdy and welcome to the forum....just a few things to start....you might want to make your Subject a little descriptive. Also, using code tags around your code make it easier for us to help. These are both rules of this forum and a Moderator will probably come and let you know....I'll see if I can help when those things are done....check the FAQ's for using Code tags....

[JOHN H. DAVIS]

Welcome to the Forum. Please review the Forum Rules for guidange in posting. Especially Forum Rules No. 1, and 3.

[mDevel]

Sorry guys, thank you.

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[mDevel]

Thank you. Fixed.

[mDevel]

anyone have any advice? I just want it to perform the filters and fill column B with my value rather than me having to manually enter it. The number of rows changes every time, so how can I get it to fill down to the very last row of the column? When I run the macro on a different spreadsheet, the pattern of column B matches the pattern of the spreadsheet where the macro was created.

I dont want it to do this for every cell:
Range("B5").Select
ActiveCell.FormulaR1C1 = "N"
Range("B6").Select

[JOHN H. DAVIS]

Does this help?

Without Filter

Please Login or Register  to view this content.

With Filter

Please Login or Register  to view this content.

[mDevel]

Does this help?

Without Filter

Please Login or Register  to view this content.

With Filter

Please Login or Register  to view this content.

Thanks, I'll try it. Would I need to replace this part of the code

Please Login or Register  to view this content.

in replacement for one of the codes above?

[JOHN H. DAVIS]

How far down do you want to fill it? Where do you want to start? What are your parameters?

[mDevel]

Does this help?

Without Filter

Please Login or Register  to view this content.

With Filter

Please Login or Register  to view this content.

Does this help?

Without Filter

Please Login or Register  to view this content.

With Filter

Please Login or Register  to view this content.

Thanks John, the Macro works. For some reason, with the filter, some rows in the column that I want to fill are being skipped. Would you have any advice?

[JOHN H. DAVIS]

Can you attach a sample with rows that are not being picked up? I'll take a look. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[mDevel]

I figured it out. Thanks.

I have another question. I want to set the range to read the last row of the column. I'm going to be using this macro for other spreadsheets where the number of rows changes every time.

This is my code:

Please Login or Register  to view this content.

Would I replace the ActiveSheet.Range("$A$1:$R$214").AutoFilter with something like the code below?

Please Login or Register  to view this content.

[JOHN H. DAVIS]

One way:

Please Login or Register  to view this content.

[mDevel]

One way:

Please Login or Register  to view this content.

Thank you!

One last thing,

I've tried to run the macro on another worksheet, but I got an error that highlighted Sheets("10-9").Select, I believe its because the sheet name of the other document where I'm trying to run the macro on is different. "10-9" stands for the date. The date of course will vary on each excel document that I work on.

Would I need something like this?

Dim x As String
x=ActiveSheet.Name

In replacement for the code that is in red?

Please Login or Register  to view this content.

[JOHN H. DAVIS]

If you want the code to run on the activesheet then you would just delete those lines. Not sure about what is causing the error. What does the error say?

Is this what you're trying to accomplish? Try on a copy too see first.

Please Login or Register  to view this content.

[mDevel]

If you want the code to run on the activesheet then you would just delete those lines. Not sure about what is causing the error. What does the error say?

Is this what you're trying to accomplish? Try on a copy too see first.

Please Login or Register  to view this content.

I really appreciate your contribution John.

So whenever I'm trying to run the macro on a different sheet, for example:

When I open a new excel spreadsheet, the title of the sheet is going to be different from the title of the sheet where the Macro was created. The code below shows that the title of the sheet is "9-24", which is the ActiveSheet where the macro was created.

Sheets("9-24").Select

What if I were to open another excel spreadsheet where the title of the sheet is not "9-24" but "10-9" for example, or if the title of the sheet is just "sheet 1".


When I try to run the macro on a sheet that is named "10-9"

I get the error:

"Run-time error '9':
Subscript out of range


and when I debug, it highlights the code in yellow, which is indicated in red below.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Did you test the code I provided? It seems like you are using the original code instead. Also a Subcript of Range normally indicates that excel cannot locate an object. Since Sheets("9-24") is highlighted, I suspect that the sheet name is causing the error. Are you sure it isn't supposed to be Sheets("9 - 24") maybe with spaces. Check your sheet name.

[mDevel]

Did you test the code I provided? It seems like you are using the original code instead. Also a Subcript of Range normally indicates that excel cannot locate an object. Since Sheets("9-24") is highlighted, I suspect that the sheet name is causing the error. Are you sure it isn't supposed to be Sheets("9 - 24") maybe with spaces. Check your sheet name.

Yes, I tried the code. It didn't work the way I wanted it to.

If I open up another excel file, how can I get it to read the name of the worksheet of that file. When I open a new file, the macro is thinking that the worksheet name of the file is "9-24" (which is the worksheet from the previous file) rather than the actual sheet name of the new file that was opened. I would have to go back to the VBA code, change sheet name from the previous file to match the sheetname of the new opened file.

So for example:

"9-24" is the sheet name from the previous file

I open up a new file, the sheet name of that file is either "sheet 1" or another name.

When I run the macro, I get an error and "Sheets("9-24").Select is highlighted because that's not the name of the sheet where I'm running the macro on.

I would have to go back into the VBA code to match the title of the sheet myself.

How can I bypass that or, get it so that it reads whatever the title of the sheet name upon opening it.

[mDevel]

I'm actually copying the table from "9-24" to the "Exception" and "Boarded" sheet. "9-24" is not going to be the same sheet name for the other excel files where I'll be running the macro. How can I code it so that it reads "x" name of another active sheet on a different file?

Please Login or Register  to view this content.