[Urgent] how to reference VBA range on filtered cells & questions on feasibility of reorganising cells

[I_love_excel]

Hi, Just want to say thanks in advance, Saturdays are (or at least should be!) a day of rest!

I'm currently interning and need to deliver some "improvements" asap as my probation is in a months time and I could really do with the job. I've identified two improvements that I can deliver, and have been cramming VBA for the past week.

Currently I'm using the VBA recorder and then googling for snippets more relevant that I can then use and try and guess. It's going okay, but I'm running out of time.

So my questions to you fine people is:

1) Is it possible to have a report pulled (I'm given an excel file) (column amount will always be the same (A-BK) ), rows are variable (circa 1500-4000). The only things that needs to happen is that the macro will:

• Will filter one column removing all results with a specific variable eg "apple"
• Will filter another column which will filter one variable. From those results I need it to then change all the values in another column to a specific value ("true").
• The filter is removed, and then the columns are rearranged in a specific order.
• The data is then filtered on a specific column. There are 4 specific variables and then the last variable is the remainder of whatever is not picked up in the last 4 variables.
• Each variables results take the first 10 columns which are are saved in a new worksheet, saved.
• All the 5 excel sheets are then emailed in one mail to a specific email address.

The main criteria here is that I can standardise the process so that it is rock solid, if it did it wrong then it would have serious repercussions. I just want to make sure that its properly possible to do before i commit any more time.

2) I've got a huge data file that comes in every day in excel. I simply need to be able to filter it by 5 variables, and then copy and paste each variables result into a new sheet and then order it by importance. I've made the below which perfectly colour filters it (I mistaken thought this was a solution to the problem originally). Can I modify the below or would I be best starting again.

P.s on a general basis, even though it works, I'm aware as a newbie that I'm probably doing some things wrong. Any glaring mistakes or better practices for the future regarding the below?

Furthermore, though its relevant to the help needed in above questions, does anyone have a good guide on how to use autofilter & VBA to reference just the filtered results rather than trying to give random ranges of like a1-bk2000.


Thanks again for any assistance, much obliged!

 

[mrmmickle1]

• Will filter one column removing all results with a specific variable eg "apple"
• Will filter another column which will filter one variable. From those results I need it to then change all the values in another column to a specific value ("true").
• The filter is removed, and then the columns are rearranged in a specific order.
• The data is then filtered on a specific column. There are 4 specific variables and then the last variable is the remainder of whatever is not picked up in the last 4 variables.
• Each variables results take the first 10 columns which are are saved in a new worksheet, saved.
• All the 5 excel sheets are then emailed in one mail to a specific email address.

You can definitely solve the issue you have.


This Line off code is starting a loop that will go through all cells in a block.... For example if you had 2000 rows of data. YOur essentially looping through every cell in a 2000 x 26 Grid....which is 52000 cells. :

[COLOR=#0000ff]For Each[/COLOR] c [COLOR=#0000ff]in[/COLOR] ActiveSheet.Range("A3:Z" & Range([COLOR=#000000]"A" & Rows.Count[/COLOR]),End(xlup).Row)

I don't think that's what you want to do.

You probably mean to do this instead:

[COLOR=#0000ff]For[/COLOR] i = 2 [COLOR=#0000ff]To [/COLOR]Range([COLOR=#000000]"A" & Rows.Count[/COLOR]),End(xlup).Row [COLOR=#008000]'2 to 2000 if you have 2000 rows....[/COLOR]

Hope this helps.


If you post your questions one at a time with specific examples you will get a better response. It also helps to post the actual code in text rather than as an image so people are able to copy and paste into the Visual Basic Editor and Debug your code. The 6 questions you posted would take a considerable amount off effort to complete. That being said, people are more than happy to help you learn. If you start small(with one or two questions) you can build on your knowledge. This may prevent the other questions from needing to be asked.

For the last question I will recommend that you look at Ron De Bruin's website. He has more than a few examples of Email VBA that you can modify for your needs.

Mail from Excel and make/mail PDF files (Windows)

 

[I_love_excel]

Thank you very much Matt, appreciate the help. I thought my code was a bit sloppy! Reading over my questions it was a bit of heavy number! I'll start another thread with a specific question to get more help and then hopefully work through that to answer the other questions.

Thanks again!

 

[mrmmickle1]

I_love_excel,

Glad to be of assistance. Hopefully I was able to steer you in the right direction!