Worksheet Loop not working when moving between other Workbooks

[Strife]

Basically I'm grabbing data from a workbook someones sends, looping through all the worksheets within the workbook, and pasting the data to a master workbook in a database format so I can upload to SQL. Also the worksheets that I get sent are not always the same name each time they are sent.

I've used 'For Each ws In ActiveWorkbook.Worksheets' before, but only when doing something to one workbook and not referencing any other ones. Apparently when I'm using that loop now, everything runs correctly but it's only grabbing data from the first worksheet, and not moving to the rest.

e.g. If I have 10 worksheets it'll loop through 10 times, grab the correct data, but it will be the data from only the first worksheet pasted 10 times.

Here is the basic code, basically I was hoping there was some type of reference I could use to store the name of the current worksheet into a value at the beginning of each loop, so I could reference it using Sheets(name).Select. I tried ws.Name and ActiveSheet.Name but those didn't seem to work.

Please Login or Register  to view this content.

So all the actual code works, it's not too complicated. But I can't seem to get it to move to the next worksheet. It will just keep grabbing the same data from the first worksheet until the loop finishes.

Thanks.

[JBeaucaire]

Here's the first part of you macro back, I'm showing you how to set OBJECT references (not strings) to specific workbooks and worksheet names. Once you have put a wbobject into a variable, you can use the variable to send command directly to the wb, no need to activate or select anything

Once you've set a worksheet object variable to a specifically identified worksheet, you can then use that simple reference to address the sheet directly, even if it's in the non-active workbook. Most commands work just fine if you fully identify the target this way.

This should get you going and you can continue the technique with the rest of your macro:

Please Login or Register  to view this content.

[Mordred]

Hi Jerry, I just want to jump in with a question if that is alright? Is

Please Login or Register  to view this content.

necessary here? Should that be standard for all procedures? I'm asking because the procedure's task doesn't seem to warrant that. Or does it?

[JBeaucaire]

Hi Jerry, I just want to jump in with a question if that is alright? Is

Please Login or Register  to view this content.

necessary here? Should that be standard for all procedures? I'm asking because the procedure's task doesn't seem to warrant that. Or does it?

The OP excluded a lot of code, I would trust these are all good things to add in this instance.

No, it is not standard practice to add all these procedures. In this instance, turning off Screenupdating would be a good thing to do with badly written code that "activates" and "selects" a lot of things, you would turn off a lot of screen "jiggle" this way.

But by writing better code that doesn't activate/select, you're already improving the efficiency of execution by a great magnitude and eliminating the flicker problems at the same time, at the source.

Still, I tend to turn off screen updating still quite often when there is a lot of cell updating going on.

Only you know by your code if turning off alerts is necessary (often is) and whether you need to disable any "event macros" that would be triggered.

[Strife]

Thanks for the reply Jerry. Thanks for teaching me about the object references, I've pretty much been teaching VBA to myself when I need it for work so my stuff it's pretty messy, but this cleans it up quite a bit. I'll try to use it in the future.

The only issue is that the "CABDMRow" isn't actually part of the active workbook. That's actually a third workbook which I only reference once to grab component names. Basically after I grab all the data from the activeworkbook it's set up in such a way where I just grab a set component list to match up to the values. i.e. AutoGoal, AutoTotal, etc.

So I'm still not sure how to reference the worksheet that is currently in the loop.

Here is what I mean based on your code:

Please Login or Register  to view this content.

Basically I added a reference to the third workbook which matches with the "CABDMRow". But for grabbing the employee ID it goes back to the active workbook worksheet it is looping through. I wasn't sure how to store that active ws to reference it? (see the second to last line of code)

And this worksheet will change through every loop since I need to reference it to grab the employee id of each worksheet.

I wasn't sure if using these object references never causes you to actually go to the other workbooks, so would I just do a simple Range("B3").Select, Selection.Copy?

Thanks.

[Strife]

Oh nvm I got it, just had to add a ws.Activate at the beginning of the loop and that did it, and I changed the reference:

wbDATA.Sheets("CABDMRow").Range("B3").Copy

to simply:

Range("B3").Copy

This object referencing instead of string helps a bunch, I'll use it going forward. Thanks so much for teaching me.

[JBeaucaire]

To reference the worksheet that is currently in the loop without the pesky "activating", you again add the ws object to your references.

Please Login or Register  to view this content.

See, it's the same thing. Once the worksheet is referenced in a variable it can be used as a parent object. The cool thing about the FOR EACH loop is that it keeps changing the worksheet objects for you each time through the loop.



This can get even faster if you can eliminate the COPY/PASTE commands. Since you're only wanting the values to appear in the new cell, you can use the .Value property to transfer that value without ever invoking the sheet-level "copy" function.

Please Login or Register  to view this content.

[Strife]

Oh good to know.

Yea I've been using .Value = for actual hard coded values in my macro or when its stored in a variable, but I never thought about using it for actual cells instead of the copy/paste function.

This thread gave me alot of good information for optimizing my macros, thanks so much for your time.

Do you happen to know of a good book or materials that teach stuff like this? I use VBA a moderate amount for automating stuff at work, but it's all just self taught, searching the web, etc. So alot of my stuff is probably much slower and less efficient then someone who really knows VB, although it's still way faster then doing it manually. I'd also like to try to use it more in things outside of Excel...

Thanks.

[JBeaucaire]

I use this forum, it's a pretty thorough book, and the authors talk back with you.