On 06/01/2015 20:46, William W. Viergever wrote:
On Mon, 05 Jan 2015 23:52:22 -0500, GS wrote:

What do you mean when you say "new tab names"? If you're referring to
worksheet tabs then your project needs a serious overhaul if it doesn't
work because a user renamed a sheet! OR do you intend to distribute a
'bandaid' for every user who renames a sheet?

Better (IMO) to properly rebuild your project!!!

hi grary - thanks for the reply

i guess i was trying to be too parsimonious; sorry

<verbose on :-)

these hospital sepcific spreadsheets contains about 30 tabs of
finacial data based on a report the hospitals submit to the State ..
they have to submit this reports 45 days after fiscal year end ...
what we do is, a year or so later, take Medicaid paid claims (and AR
and denied days data from the hospitals) to get an exact Medicaid Net
Revenue (which resides on yet another, non-State-report, "tab") and
uise that to "redistribute" everything (based on existing ratios,
etc.) not only across all the Medicaid financial categories, but also
across the different payors (e.g., Mediicare, Commerical Inusrance,
Self-Pay, etc., such that Total Net Revenues, i.e., across ALL
payores, remains the same) ... and then we re-subnmit this "revised"
report to the State (note: this State mandated financial report is
used to determine qualification for a Mediciad "Disproportionate
Share" supplemental reimbursement pool (about 140-150 out of 450+
Calif. hospitals typically qualify for about $1.1B in supp revnue)
.... so, in my hosp-secific spreadsheets, think of all those tabs as
original and final ... and i send these off to a couple of other
consultants (who do the in-hospital data pulling, and final assembling
of all data)

as such, there's all sorts of "buttons" that do certain things to make
life easier for these other guys

and for one of those buttons' code, i forgot to update it to reflect
the new (fiscal year) tab name, CY13 Final:


Sub CopyFinalRevsToNewTotals()
'
' Copy Revs from CY12 Final to 12 New Totlas
'
'
Sheets("CY12 Final").Select
Range("B8:X19").Select
Selection.Copy
Sheets("12 New Totals").Select
Range("B92").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

so ... since i had sent out 36 of these already, i was hoping i could
send them an empty spreadsheet with one simple macro that they could
then call, from within a given hospital-specific spreadsheet, such
that it would change the above VBA macro to read:

Sub CopyFinalRevsToNewTotals()
'
' Copy Revs from CY13 Final to 12 New Totlas
'
'
Sheets("CY13 Final").Select
Range("B8:X19").Select
Selection.Copy
Sheets("12 New Totals").Select
Range("B92").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

as there's no way in #33lLthey'd want to open the VBA editor and do
their own search and replace (trust me, they'd balk AND i've got the
VBA locked down since it's all my proprietary code)

note: in the above it is only CY12 FInal that has changed to CY13
Final ... the "12 New Totals" refers to Page 12 of the financial
report

anyway - as mentioned in my original post, in the past, i've done
something similar (i.e., sned them empty spreadhseet w/ "fix" macors
they could run frm their end) but only to change (e.g.,) formulas in
misc. tabs, etc. ... never to change the underlying VBA code

</verbose off :-)

thanks again

over & out


One way to do this is to isolate the things that can change regularly
like the year number and build the filenames and headings up from that.
If you only have to make a single change of year number then the scope
for parts getting out of sync is much reduced.

Why can't you just send them a corrected version?

Or a patch file to alter just the latest differences (old school method
from the days when updates had to go down a dialup modem).

You can use VBA in one project to alter the code in another or to add
code to a newly created workbook but you would need to test it very
carefully to make sure it does exactly what you want.

--
Regards,
Martin Brown