Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

**Seth_** · 11-09-2016, 12:00 PM

Good morning,
My manager has tasked me with building a reporting automation piece for our biggest client. Every month, they want to have an updated units and volume count for the various loan programs they offer. 1 report will be run out of our originations system and the values from that report will need to be placed on a year to date master report that contains columns for every month and a running total. I've already built this master report template, but I'm running into difficulties setting up the code that will read the monthly originations report. It appears that I will need to use nested loop statements to make this work, and since I've only used single loop statements, I suspect my syntax is off. The code is below for just one loan program. Once I figure out the correct syntax, I can replicate for all loan programs involved. The Tname spreadsheet is the originations report and ThisWorkbook is template that the data needs to be copied into. I'm trying to use active cell properties on each spreadsheet and perhaps that won't work? I'm not sure. Any assistance will be appreciated!

Please Login or Register  to view this content.

**dflak** · 11-09-2016, 02:13 PM

I don't think anyone is going to tackle this issue without a sample workbook(s). Make sure all data is "scrubbed" so no proprietary information is revealed.

**Arkadi** · 11-09-2016, 02:22 PM

ActiveCell, ActiveSheet and ActiveWorkbook are handy on occasion but not always the best way to go especially when you are working with multiple sheets or books. That said, I am having trouble understanding why you activate a particular window, select a sheet, activate a cell on it, and then go and activate ThisWorkbook then a sheet in it, and then a cell within that. Whatever is active last is the only active thing so the activate of Tname, Table, C2 bit seems to have no purpose?
I also don't get the meaning of "Windows(Tname).ActivateCell = ThisWorkbook.ActivateCell"... ActivateCell is not a property or function? I imagine that line would result in an error like "Method or datamember not found"?

I suggest you work with range variables instead of active cells, though as mentioned without a bigger context it is hard to be sure what is best or what the code is best served by.

**Seth_** · 11-09-2016, 04:54 PM

Good thoughts. I'll post the 2 spreadsheets here shortly.

**Seth_** · 11-09-2016, 05:07 PM

Here are the 2 spreadsheets. They've been stripped of identifying information, so they look a bit different. But the code should be close.

The highlighted portions of the template spreadsheet are what the macro should be identifying and filling in with values starting in cell D14. Ideally, it will identify which month the funded report
has been run for and then select that month from the column headers across the top and fill in those cells. Active Cell may not be the way to go about this, I'm just not sure what to use. If the Funded
Report is the active worksheet and the Do Until condition is met, what syntax do I use to copy that cell and paste into the other spreadsheet at the correct field? So I guess the question is a two part
one. How to use the nested loop syntax, and then how to copy and paste as per the above?

**dflak** · 11-09-2016, 05:40 PM

It looks like the loans are called one thing in one workbook and another in the other workbook. This means we'd need some sort of translation table can you provide one?

**Seth_** · 11-09-2016, 06:22 PM

Yes, I can. Just for illustrative purposes, the below should be sufficient.

Values in the "10 YEAR FIXED SUMMARY" band should be copied and pasted into the appropriate cells for the "10 Year - Fixed"
Values in the "10/1 LIBOR ARM SUMMARY" band should be copied and pasted into the appropriate cells for the "10/1 ARM"
Values in the "15 YEAR FIXED SUMMARY" band should be copied and pasted into the appropriate cells for the "15 Year - Fixed"
Values in the "20 YEAR FIXED SUMMARY" band should be copied and pasted into the appropriate cells for the "20 Year - Fixed"
Values in the "25 YEAR FIXED SUMMARY" band should be copied and pasted into the appropriate cells for the "25 Year - Fixed"
Values in the "30 YEAR FIXED SUMMARY" band should be copied and pasted into the appropriate cells for the "30 Year - Fixed"

**dflak** · 11-10-2016, 12:48 PM

I did a quick QA on this - it runs and the January figures look OK.

I added a couple of sheets to the report.

Translations - you will have to complete filling in the translation tables. I use this table in a VLOOKUP on the data page to match the names on one report with the names on the other report (see last column of data).

Data - I decided to copy in the data from the other report rather than attempting to work across open files. The code may run faster because of this. I use the Data Table as the source for the pivot table.

Pivot - I generate a month number based on the column number on the report sheet. I set the month number to the filter in Cell B1 on the Pivot page. The pivot table has some N/A on it because the Translation Table is not complete. Likewise the Report Sheet has a lot of empty spaces since the Translation Table is incomplete.

Column A is the New Loan Program - that is the Loan Program name expected by the Report Sheet. I use the Find command to find the name. If the name isn't on the list, I do nothing. If the name is on the list, I get the two amounts and put them on the Report Sheet.

**Seth_** · 11-10-2016, 02:18 PM

Thanks dflak, I'll have to take some time and work through your setup here. But I like using the table as the translation and also pulling the funded report in as a table on the Data tab. One question here, you filled out the months to number 9 (September). Say this template is saved and then October is run later. How can the code flex to allow for another month's data to be added? or does the month number on the pivot page address that?

**dflak** · 11-10-2016, 03:08 PM

The months are driven by the data on the data page.

Please Login or Register  to view this content.

I actually attempt to read all 12 months. That's with the MonthNum = (jR/2) -1 does. It computes a month number based on what column I'm in.

However, I do a check to see if the MonthNum actually exists in the data: If Application.CountIf(Range("Table_Data[Funding Month]"), MonthNum) > 0 Then

If you try to set a pivot table filter to data that doesn't exist, it does bizarre things. So I make sure I have at least one piece of data that fits the filter.

So we only have 9 months. As I go across the columns I process months 1 - 9. When I get to month 10 and later, there is no data for these months yet so the code skips processing. When month 10, 11 and 12 data are added, the code will pick them up.

**Seth_** · 11-10-2016, 07:06 PM

Thanks dflak, that's a cool use of a pivot table that I haven't seen before. It will take me a few days to walk through this and try some modifications on my side. I really appreciate you helping me out.
Everything I've learned has come from someone else showing me something new. I'll try to pass it forward.

**dflak** · 11-11-2016, 10:16 AM

My philosophy is to let "basic" Excel do as much of the "heavy lifting" as possible and use VB code to stitch and bolt the pieces together in a Dr. Frankenstein fashion. Pivot tables are a very powerful tool for selecting data, consolidating it and organizing it. I also overlay the results with a named dynamic range very often and then use For Each cl in Range("RangeName") ...

Here is some information on named dynamic ranges: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.

**IonutC** · 11-11-2016, 10:27 AM

Thanks for sharing this Dflak!

Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

LinkBack

Thread Tools

Rate This Thread

Display

Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

Thread Information

Users Browsing this Thread

Similar Threads

Highlight Row & Column of Active cell in large spreadsheets

Nested For Loop + If Statements Error

[SOLVED] replacing nested if statements with some kind of VBA loop?

For each loop nested; getting stuck on one cell in first iteration of nested loop

Combining 3 Nested If Statements Into One Cell?

max value of a cell nested if statements

[SOLVED] multilple nested if statements in one cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions