Procedure Too Long

[Nick3535]

Is there any way to make this code shorter? I need to replicate it from every new tab I add. I think I will have about 40 of them.

Or how can I run a marco that will run the code on each sheet without it wiping out all the info it pulled on the pervious sheet?


This is what I have now:

Please Login or Register  to view this content.

[MarvinP]

Range("A3") to Range("H3") can be Cells(3,1) to Cells(3,8)

You could do a For i = 1 to 8 : ....cells(3,i) .... : next i

Also - I hope you have the code in a module and not behind each sheet in the workbook? right?

[Nick3535]

I have it in the module. I was just trying some different things. I'm new to marco's.

Would this change let the code be longer?

[Nick3535]

I dont think I now how to write that. Can you write an example?

[shg]

See if this does the same as that:

Please Login or Register  to view this content.

[MarvinP]

Hi Nick,

To show you how I'd use the For Next loop, see my code below.

Please Login or Register  to view this content.

Everybody has their favorite methods and by reading other peoples code we all learn. Shg is a real pro and I'd never suggest my code is better. The problem is that I understand my own code and techniques and can modify it easier than understanding others.

I need to start using shg's For Each looping as it is cool. I just grew up before this was allowed. I'm stuck with a better understanding of For ... Next.

I hope having 2 examples of what you did helps.

[Nick3535]

Thanks MavinP and Shg. This is great.

Shg
So if I wanted to add this code to include worksheet "J. Bobango", would I just copy and paste everything below the first set and change the worksheet name?

[shg]

It does it to all worksheets except sheet All.

[Nick3535]

When I run it, it only pulls information to "King Rogers" sheet.

[shg]

When I run it, it only pulls information to "King Rogers" sheet.

That's true. I don't know what else you want it to do.

[Nick3535]

It needs to do the same thing in everysheet at the same time without wiping out the previous sheet.

[shg]

Nick, I have no idea what that means. Maybe Marvin understands.

[Nick3535]

I have more than one sheet in the workbook.
"All" has the information I need. "King Rogers", "J Smith" , "Nick"...... is where I need the information to copy to. The information from "All" that needs to be copied depends on what numbers are in the A3:H3 cells of the other sheets. Sorry for the confusion. I tried to attach my workbook but the it will not let me for some reason. It keeps telling me to refresh the page.

[shg]

Do the cells A3:H3 contain the exact names of the destination sheets?

[MarvinP]

Marvin understands very little.
BTW - I should have suggested pivot tables, you are sounding like me.

[jaslake]

Hi Nick

If this relates to your previous post, try zipping your file. You've got a lot of formatting going on in your file such that it may be too large (although you should have gotten a message to that effect). Try it, it may work.

John

[Nick3535]

In the "All" sheet in Column C there are ID numbers. If one of those ID numbers is entered in A3:H3 of the other sheets I need all of the row to be copied to that sheet.

[jaslake]

Hi Nick

Having seen your workbook in a previous thread, I've a sense of what you're trying to accomplish. I've modified your posted code very slightly so that it cycles through every worksheet in the workbook and performs the same task on each sheet. I've not attempted to make the code "shorter" but you don't need to recreate it for each sheet.

Hope this helps.

John

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

[Nick3535]

Thanks everyone

snb- i am getting an Run time error 1004 - Autofilter method of range class frailed


Jaslake- i havent tried yours yet.

[Nick3535]

Jaslake- That worked perfect. Thanks for your help.
This is off this topic and my be hard for me to explain: I need a code that will make an weighted average rate? I have a list of the different customers account. Each row starting at row 5 and information about the accounts. In column B it tells what type of account they own. If it says "Note" then I want the amount in the C column to be divided by $A$1 then multiplied by the number in the D column of the same row. The products of all the different "Note"s that were calculated needs to be added together in B2.

I'm not sure if you can understand my terrible explanation but any help is better than what I've got.

Thanks again
Nick

[snb]

Look for the difference...

Please Login or Register  to view this content.

[jaslake]

Hi Nick

You're right, it's off topic. I'd suggest you start a new thread. If you attach a copy of what you have and what you'd like it to be, you'll be certain to get quicker response with a solution tailored to your needs.

John

[jaslake]

Hi snb

I found the difference but I had to work at it. How cryptic.

John

[Nick3535]

Havent had any luck finding the difference. Could you tell me?

[snb]

Sheets("All").usedrange.columns(3)

[Nick3535]

I dont know enough about this to figure it out. I probably need to take some kind of class so I can better understand these things. I never once looked at any macro's before a few ago. My boss told me about this report he wanted and this is the only way I thought it could be done. Anyways thanks for the help.

[jaslake]

Hi Nick

I'll be happy to help you any way I can. Regarding this

Please Login or Register  to view this content.

I indeed found the difference

Please Login or Register  to view this content.

and got this error message when the "corrected" procedure is run against your file

Run time error 1004 - AutoFilter method of range class failed

I haven't tried to debug the code because it's too cryptic for my limited knowledge. Perhaps snb can debug using your sample file at http://www.excelforum.com/excel-prog...uotfilter.html

Regarding this

I need a code that will make an weighted average rate? I have a list of the different customers account. Each row starting at row 5 and information about the accounts. In column B it tells what type of account they own. If it says "Note" then I want the amount in the C column to be divided by $A$1 then multiplied by the number in the D column of the same row. The products of all the different "Note"s that were calculated needs to be added together in B2.

Please start a new thread and attach a copy of what you have and what you'd like it to be. You'll be certain to get quicker response with a solution tailored to your needs.

If you wish, PM me when you've done so. Help us, we'll help you.

John

[jaslake]

Hi Nick3535

I see you got this issue resolved

I need a code that will make an weighted average rate? I have a list of the different customers account. Each row starting at row 5 and information about the accounts. In column B it tells what type of account they own. If it says "Note" then I want the amount in the C column to be divided by $A$1 then multiplied by the number in the D column of the same row. The products of all the different "Note"s that were calculated needs to be added together in B2.

Good for you.

John