Need to change code that loops all sheets TO exclude some sheets.

I agree with Dom

However if you begin your code on these lines

Please Login or Register  to view this content.

Why this?

Please Login or Register  to view this content.

Then doms' comments

No need to agologise for being a beginner.

We are all here to help if we can, sometimes even the masters find out something they didn't know!

I'll look at your problem again.

Cheers

Thank you Marcol!
I hope I explained it correctly. I am studying more right now. I ran your code and it worked except for my same problem, "looping" thru ALL the worksheets and I want it to either loop certain sheets or exclude certain sheets.
Thank you!

Yes, Marcol, that is what I want to do.

I have worksheets that contain the data I want to get.

"Jan" "Feb" "Mar" "Apr" "May" "Jun" "Jul" "Aug" "Sep" "Oct" "Nov" "Dec"

I have worksheets where the macros put the results. I have different macros for each worksheet.

"CF" "Common" and "Emp"

Some of the results on "CF" "Common" & Emp" may be the same results, so when I run the macro on "CF" for example, the macro will also get data (results) from the other worksheets "Common" and "Emp" ( I don't want it to.

So if my macro goes thru all the worksheets, I will get duplicate results on my "CF" "Common" and "Emp"

Keep in mind, for example on

"CF" I am getting data where Col A = "1" from ALL worksheets

"Common" I am getting data where Col B = "C1" from ALL worksheets

"Emp" I am getting data where Col A = "E" from ALL worksheets

So if a row (in Jan-Dec worksheets) has "1" in Col A AND "C1" in Col B
the results will show up in worksheets both "CF" and "Common" therefore the they will keep picking up each others results instead of just getting the results from Jan-Dec worksheets.

Therefore results in "CF" "Common" and "Emp" could contain the same data.

When I run these macros, I want it to only retrieve it only from sheets
"Jan" "Feb" "Mar" "Apr" "May" "Jun" "Jul" "Aug" "Sep" "Oct" "Nov" "Dec"

and exclude "CF" "Common" and "Emp"
One of these is already excluded for example, when I am running it on "Emp"

Please Login or Register  to view this content.

I have many macros that vary getting results for "1" "2" "3" etc on worksheet CF
and many combinations. They all work great except for getting data from worksheets that I don't want it to.

Wow!....

Thats' a bigger animal than I thought! And you're a beginner?

I can't look at it tonight, I'll give it a go tomorrow, look back after 12:00 GMT, we are on BST at the moment I think you are about 7hrs behind us. Please bear this time difference in mind

Cheers
Alistair

Sorry
Of course, take your time and don't worry if you don't have time to look at it at all.
You are helping me.
Basically, I just do not want the macro to get info from ALL sheets. I want to exclude the worksheets that I am sending info to.
Please don't worry if you don't want to fool with it.
Thank you!

Okay sherrie give the attached workbook a try.

The code reads only from Sheets("Jan") through to Sheets("Dec") any other sheets are ignored, including any you might want to add later.

1/. I have trimmed your code a bit by writing one general sub for each sheet ("CF","Common","Emp") and then called the specific routines through your existing buttons, step through one to see how it works

Basically if you find yourself pasting the same thing more than once, consider writing a general sub or function.

2/. You should make your subs private if you don't want them to show when you go Tools > Macro > Macros........... This way they are only accessable through the buttons.

3/. I have added some scrolling control to automatically take you to the range you are working with.

4/. I have hidden all unused columns and rows, this makes the sheets more user friendly.
It helps to prevent scrolling into unused areas.

I could see you were trying to Hide/Unhide Rows/Columns so I put in some code to demonstrate this using the unused ones as an example.

5/. One issue remains as I see it.
If there are more results returned than your Tables can contain then the code will overwrite your Table structure.
I am about to rewrite the code to cover this eventuallity, but in the meantime could you check over what has been done so far and let me know what you think.


Have fun...
Alistair

Muchas Gracias Marcol !!!! It's like Christmas!
You went over and beyond! You spent so much time. Please know it is greatly appreciated. Not only my files function NOW, I have so much to study.
I will pass along everything I learn here to people here who want to learn.
It works perfectly and so much cleaner.
Thank you!
Sherrie

I don't want to take advantage. I have one more question. maybe I should start a new thread, but this is specifically with this project.
When I "get" the results on the CF sheet. I am getting for columns 1-20

Please Login or Register  to view this content.

I know I can "get" from more columns by changing to 1 To 30, but can I skip some columns?
For example get 1 To 20 AND 30 To 40 also?

If this is not a simple solution, please don't spend any more time on my project.
I thought it would be very interesting and useful to know. I won't ask more questions till I have studied more

I really hate to ask this, but I cant figure it out something. When code was condensed, I lost the ability to get different criteria

Please Login or Register  to view this content.

Now it doesn't change for each section on sheets "CF" or "Common"

"getownersdeposits" macro for sheet "CF"
It is set to get "1" for all sections on "CF" sheet
I need to get for section 1 all rows that begin with "1"
get for section 2 all rows that begin with "2"
get for section 3 all rows that begin with "3"
get for section 4 all rows that begin with "4"

"getcommon" macro for sheet "Common"
is set to get "C1" for all sections on "Common" sheet
I need to get for section "Credits for Common Charge #1" all rows that begin with "C1"
for section "Credits for Common Charge #2" all rows that begin with "C2"

So right now it gets "1" for all sections in sheet CF
and gets "C1" for all sections of sheet Common.

If you could get one to work, I could copy it for all the rest. Right now it is all combined and I don't know how to separate it back out. I am trying but no luck.
Really sorry to be so much trouble. I just got in way over my head.

Hi Sherrie,

Thank you for the complements

I have a few more ideas that may be of use to you, and I will also address your last request tomorrow.

In the meantime I must retire, It's a Scottish tradition to have more beer on a Saturday night than it is good for one! excuse me (burrp...)

Seriously, please look back tomorrow, I'm not at my best at the moment.

Slainte
Alistair

Marcol, you are a trooper!
Since we're 7 hours behind, I am just now going out to enjoy your Scottish tradition!
Sleep well.

In reply to Post#13

Please Login or Register  to view this content.

Will skip 21 to 29, but if you do not want to have empty cells in the row, ie close up the result it is a little more complicated.

Please let me know what way you want to go. Then I can answer you when I look at Post #14 (hopefully tonight).

Cheers

Hi Marcol! Hope you don't have a goma (hangover) today. I have a little one.

Post #13 No, that is perfect. No need to close up empty spaces. I was just really curious how to do it. No need to do anything else for post #13.

Post #14 is my big problem because it won't work without being able to change the criteria for different sections on the same sheet. Please let me know if I didn't explain it correctly in post #14.
Thank you again!

Hi sherrie

I feel I must post this to correct a few oversights in the last Post. and in answer to your Post #14

I did send a PM but no reply

I didn't notice all the variables in the modules getownersdeposits and getCommon

I have changed both to typically to this (getownersdeposits)

Please Login or Register  to view this content.

Sorry for any delay and breakdown in communications

Updated workbook attached

FANTASTIC!
It worked great! Muchas Gracias!