[A good cause. Long.] Need help from Excel gurus

[Heretic Apostate]

I'm trying to design a spreadsheet macro so that it will go to a cell, place it in the top left corner of the screen, skip to another place on the screen, and freeze the window pane.

The trouble is, sometimes the cell I'm trying to select ends up at the bottom left, sometimes the middle of the screen, but never where I want it to be.

Can someone tell me of a macro command that places the selected cell at the upper left of the screen? And one to prevent the window from moving while you move to another cell?

(I may be about to take the test for Expert Excel MOUS, but that doesn't mean I know about Visual Basic. )

Edit: Might get some answers if I post what I'm doing this for. Here in California, there are massive budget cuts coming up. One of the departments at the local county offices that is facing staff reductions is the accounting department of the child support services. They need a manager, two analysts, two senior account clerks, and four account clerks to keep up with the level of work. However, they only are allowed one of the four account clerks they have, and she is going to be graduating soon and getting a job elsewhere--and who knows how long it'll be before they can find someone who can fill her shoes?

Anyway, I worked in that office for two months (before it was mutually decided that, while I'm good at accounting, they needed a clerk instead), and saw that they were doing audits of cases manually. I won't describe the process to y'all, but basically when I got there, it would take up to eight hours to process the data on the latest system, but after I made a spreadsheet for them, it reduced the work down to less than 15 minutes, and with a lot more options as well.

The trouble is, since I've learned more about Excel, I've come to see my spreadsheet as bloated and slow. I set it up to process 1,800 lines of data, and each line of data goes through 27 calculations. This isn't bad when you're only processing about 100 lines of data (you enter 12 lines of data at a time, each time taking about 30 seconds; then it takes 30 seconds to put in the limits and stuff), but I recently tested it with 500 lines of data, on a 733MHz Pentium III (better than the county has), and it was VERY, VERY slow.

So I looked at what I knew of Excel from my latest training, and hit on using Advanced Filters. My basic premise for the redesign was to never use formulas if I could avoid it, which means everything is on one page (so I don't have 20,000 formulas directed to the Summary, Breakouts, and Detail pages).

Which is why I'm trying to set up the macros to move around the screen, and position it perfectly. I want the relevant data easily accessible (the spreadsheet is now about 7500 rows by about 50 columns, so finding the data without macros would eat up the time I'm trying to save).

It's for the children. Plus it's for my friends in the accounting department. I can conquer every other aspect of the spreadsheet, but I can't figure out this one little problem...

 

[dellix]

Don't know if this is what you want but here goes.

Range("E14").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
Range("D130").Select

This will take the data in cell e14 and place it in cell a1 then goes to cell d130 and stops.

Hope it helps.

 

[Heretic Apostate]

Here's an example of what I'm trying to do.

The summary page (what is used by the acctg. department for the audits) starts in cell Y31. I'd like, when the macro gets there, for cell Y31 to be the upper-left cell on the screen. (Not moving data, but moving the view.)

The trouble is, while the macro DOES go to cell Y31, the upper-left cell might be W12. So if I were then to do the freeze pane on cell AC37, they'd only be able to view 3.5 rows at a time (and half the data would be off the screen to the right), with lots of wasted room at the top and left.

I've got to do this for the Summary (breaks the data down by month, within each month by state and federal tax intercepts, and by what money went to nonwelfare, welfare, foster care, and other), the Specifics (choose what to search for, and get a non-detailed summary by month), two Detail tables (Detail Other, which gives details of the other column from the summary by month or for all of it, and Detail All, which gives a list of all relevant transactions within a month), and a manual data entry area (a 36 x 5 table that dumps into the Summary table; used for a special welfare program).

The spreadsheet has to show the data entry table, the options area (set up obligations, start dates, end dates, payment exclusion ranges by dates, and standard limits to audits), the summary, specifics, and detail tables. The rest of it, I plan on hiding until needed by the macros.

I can easily move the options area to another tab without adding any formulas. The summary tab would require 6 x 132 additional formulas. The specifics would require 4 x 132 additional formulas. Those wouldn't hurt too much to move (1,320 additional formulas, less than me adding one more calculation to the original data). The TANF (36 x 5 table, with no additional calculations wherever it is placed) wouldn't hurt to be moved, either.

The detail pages, however, will have 1,800 lines x 2 tables, each of which would have 8 pieces of data I'd want to transfer over. So these definitely have to stay on the data entry tab.

Do you think it'd help if I put the detail tables at the top of the tab? Like, for instance, starting on row 1, instead of row 3620?

 

[dellix]

I thought I did big spreadsheets, WOW!

Anyway you could create a custom view then call it using

ActiveWorkbook.CustomViews("viewname").Show

Problem being if there are multiple views you'd have to create multiple custom views which you could create in another macro. But, it would be time consuming.

 

[Heretic Apostate]

I thought I did big spreadsheets, WOW!

It's not as impressive as it sounds. I just use a LOT of advanced filters.

First advanced filter, I weed out all lines of data that don't have a payee (because, if the money didn't go out to the custodial parent or foster care, we don't want to count it), belong with this particular case (because one person can have many, many, many cases, unfortunately), and that match the source, account name, payee and case limits set by the user.

After that, I've set it up so that the user has the option to define what sort of audit they're running (e.g., for support, it can be child, family, spousal, or medical, or all of the above; for welfare type, it can be nonwelfare, welfare, or foster care, or all of the above). That's filter numbers 2 and 3. The main data table is after filter #3.

Then, since sumifs and conditional sums can't handle searching within a portion of text string, but rather have to compare against the whole text string, I've done a filter to pull out just payments from state tax intercepts (#4), federal tax intercepts (#5), payments that are paid to the custodial parent (#6), payments that go to repaying welfare grants (#7), and payments that go to repaying foster care (#8). All of these pull from filter #3.

The first eight filters are all summarized in the Summary table.

Then there are four filters (pulling from filter #3) that give specific amounts by month. For instance, let's say you want to search for all arrears (back balance) payments. Well, it's set up that you just type in "ARRS." The vlookup table looks up ARRS, finds out that it's found in the Account Name field, and tells the advanced filter to only include lines of data that have ARRS in the Account Name. Similarly, it's set up to look for payment sources (IRS, FTB (state tax), wage withholding, liens, bankruptcy, unemployment, disability, etc.) and payees (e.g., in a particular case, payee #1 might be the mother, but payee #2 might be the maternal grandmother, and payee #3 might be foster care).

And, finally, there are the two detail advanced filters.

So that's a total of, what?, 12 filters. Each filter needs to be able to handle 1800 rows (I calculate that there's at most 1 page of data --12 lines-- per month, from October 2000 to December 2010; that's 1476 lines of data that have to be handled). So while the amount of space used is incredible, the actual number of formulas is actually relatively small.

I'm shooting for this spreadsheet to be about half the size of my previous one. That one was about 5 megs.

I'm rather proud of my first spreadsheet, and they couldn't live without it. Not bad, considering I gave it to my ex-boss as a Christmas present, eh?

Now, if only they'd share it with the other counties in California. There are (or are going to be) 30 or so counties within California who use the computer program this is made for, all the smaller counties. Imagine how much time it could save!

**********

To give a comparison to what was being done before my spreadsheet, consider that they'd go through the data five times for each month they're auditing (IRS intercepts, FTB intercepts, nonwelfare, welfare, and foster care). When you have (as of now) 31 months since the system was set up, that can take quite a bit of time. And all those calculations were done with a calculator, giving rise to quite a bit of data entry error.

It didn't help that each receipt (for money collected) could not only go to multiple account names (e.g., some might go to nonwelfare arrears, some to nonwelfare interest on arrears, some might go to welfare current support, and so on), but due to rollovers, money for previous months, and money being backed out of one account and sent to another after the fact, it could look like it went to multiple months.

Anyway, after weeks of hashing out the process with them, I figured out how to automate most of the researching of dates and calculation of totals. Now you can see what I mean by reducing an eight-hour process to at most 15 minutes.

It doesn't do anything for the archival system, however, nor the pre-computerized system. The archival system contains data from September 1991 to September 2000, and there are strange laws which affect that data. I can set up a spreadsheet to handle the data, but it would be just as manual as a calculator, and there's so much subjective interpretation it just wouldn't be worth it.

Anyway you could create a custom view then call it using

ActiveWorkbook.CustomViews("viewname").Show

Problem being if there are multiple views you'd have to create multiple custom views which you could create in another macro. But, it would be time consuming.

What's a viewname, and how do you set it up?

Edit: fixed quote tag.

 

[dellix]

Sorry I should've explained it better

First go to the cell that you want up in the top in this case y31.
Then using the scroll bars move the sheet so that the cell is in the top left corner.
Go to views on the tool bar
On the drop down you'll see custom views click it
You'll get a window click add then you'll get a input box.
In name you type in what you want to call it for the example below I used y31.
Then in your macro you can use it at any time by using the following

ActiveWorkbook.CustomViews("y31").Show

 

[Heretic Apostate]

So, for instance, I could use the custom views to tell Excel to unhide the columns and rows I want hidden and do the autofilters I'd like, without having to manually enter them in the macro?

Cool...

I learn something new every time I make a spreadsheet.

I'll give it a shot. Thanks!

 

[dellix]

Not exactly for a macro to hide or unhide rows/columns try:

To hide columns b to e
Columns("b:e").Select
Selection.EntireColumn.Hidden = True

To unhide columns b to e
Columns("b:e").Select
Selection.EntireColumn.Hidden = False

To hide rows 9 to 13
Rows("9:13").Select
Selection.EntireRow.Hidden = True

To unhide rows 9 to 13
Rows("9:13").Select
Selection.EntireRow.Hidden = False

 

[BlackBart]

This should place cell "Y13" in the top left corner. For another cell, you just need to change the 13 and 25 accordingly.

Application.ScreenUpdating = False
ActiveWindow.SmallScroll Down:=(13 - ActiveWindow.VisibleRange.Row)
ActiveWindow.SmallScroll ToRight:=(25 - ActiveWindow.VisibleRange.Column)
Application.ScreenUpdating = True

 

[Heretic Apostate]

Dang it, I was SO trying to make it fit onto one 3.5" disk. I'm up to 2.34MB (including all the data, so it'll be probably 500kb smaller when I empty it). *sigh*

Maybe zipping it will remove some of the size? How much compression does zipping a spreadsheet usually get?

***********

Okay, an update, before I take a break to clear my mind.

Somehow, I don't know why, but I thought that sumif required that it be on the same page as the data? I cut-and-pasted the summary table onto its own page (which they're used to), and the sumifs still work. Oh well, that saves about 1320 calculations, woo hoo!

I need to set up the macro for the advanced filters for the specifics table. Shouldn't be too hard, as I have the criteria already set up, and I can copy the format from the first eight adv. filters. Same thing for the two detail tables.

I need to bring in the macros for autofilter/set print area/printing from the old spreadsheet for the summary (regular and TANF versions), specifics, and both of the detail tables.

I need to create a "Clear All" macro, to set the spreadsheet up for the next case.

And I need to bring in the "Export Data" (basically, open a new worksheet, and paste in the raw data and corrected dates; when you have to research a ton of dates for a person, you REALLY want to save them for next time) from the old spreadsheet.

I should easily have this done within a couple days (since I'm taking it easy ).

Thanks, guys! I'll yank your examples for my macros.

Can y'all recommend an easily-obtainable visual basic book (like one I can check out from the library)? I'm not great at VB (I'm beyond the "monkey see/monkey do" of just recording macros; I can actually generally go in and figure out what the macro did, and adapt and edit it to my needs), and I'd like to get better.