Assistance with handling 2 or 3 workbooks in the same macro

[jmac1947]

Hello,

With the help of many people in this forum and some other research (aka Dr Google ) I have managed to get a reasonable level of knowledge in how to deal with "lots of problems" in a single workbook containing many worksheets. I am happy (and actually quite chuffed) to admit that I have even gained "rep points" for assisting many others as well.

Sadly I think my current issue is beyond my current knowledge level.

My problem, from a business perspective, is that I have a workbook (say "2014 financials") that has many worksheets which apart from a couple of special sheets are all identical in format. In that workbook I am building a macro that will cycle through the worksheets, sending an email with a data range included in the body of that email, perform a couple of "updates and save the worksheet.

So far, all relatively easy, under control, and working, mimicking existing processes in other workbooks I have built.

What I need to master is how to, from within my macro, "open another known workbook, navigate to a specific summary page (say "property register"), perform a filter and copy action and then go back to my primary macro and past the "copy data" into a known spot that I can then process "normally"

My current workbooks (the two I am trying to use) have some confidential data in them so I cannot post them right now. I am trying to get a sensible sample set of workbooks created but was just wondering if this is indeed a simple problem that many have working examples of already.

Thanks

Jmac

[Izandol]

You may use code like this:

Please Login or Register  to view this content.

[jmac1947]

Thanks Izandol for the prompt reply.

I think I follow your code suggestion (I haven't tried it yet) but a quick follow up question... does it matter to your code, in particular the

Please Login or Register  to view this content.

line if the worksheet in question is not visible when the workbook "wb" is opened??

This workbook stuff is still a bit of a "black magic" area for me at the moment

Jmac

[Izandol]

No it will not matter - as long as your code does not try to select it.

[jmac1947]

Thanks, I am still learning that you don't need to "select" everything every time (although it does make the code easier to understand when a variable value suddenly isn't what you :know it is")

Appreciate your help, will update the thread when I have chance to incorporate your suggestion and get it working.

Jmac

[jmac1947]

Hello all,

With some assistance in this thread and a PM from Izandol I have managed to muddle my way through a chunk of my problem with the following code:

Please Login or Register  to view this content.

When I run this code I get tantalisingly close to the result I am after. I do get the data back into the open workbook on the sheet i expected so that is a plus.

However I am getting back all the data from the wb.worksheets(owner_id) page.

Owner ID Own1001-Bamford
Owners (Names) Bamford, Sandra
Owner Address Line1
Owner Address Line2
Owner Address Line3
Owner Suburb Hobart
Owner Post Code 7000

Owner Email [email protected]
Owner Phone 03 6223 5626
Owner Mobile (0413) 123 456

All that I really need in the value that is in cell B11 (shown in red in the quote) in the source page and I would like to be able to assign that to a variable in my running macro.

With what I have managed so far I could work around the issue but it would be pretty clunky and I suspect I would be doing the "heavy lifting" that VBA should be doing for me.

Thanks again to Izandol for guiding me this far

Jmac

[nilem]

Hi Jmac,
maybe so

Please Login or Register  to view this content.

[jmac1947]

Hi nilem,

that works a treat thanks. The code has been applied with the correct values for the "moving parts" into the application I am building and has also been carefully filed away in my "collection of great VBA code snippets" workbook

Thanks again

Jmac