Uh.., I'm going to urge you strongly to change your approach! Instead
of inserting formulas that pull data from Sheets("Data") only to then
convert the data to values, why not just directly assign the target
cell the value? Also, you don't need to select anything to do this!

Explain the layout of the data on Sheets("Data") and where each value
should be placed on each of the other sheets. Include the account
number:sheetname list and where it resides (range.address) on
Sheets("Sheet1").

It appears you are trying to consolidate account data to individual
sheets for each account. A single sheet listing all accounts with
columns/sections for each month makes much more sense to me. This
would/could also serve as a summary sheet showing totals for month,
qtr, and YTD if desired.

If you insist on sticking with separate sheets for each account then it
would serve you much better to put the account number in a named cell
defined with local scope so it can be reused on every sheet for code
reference...

To define a range named "AcctNum" on every sheet:

Select the cell that has the account number in it;
Click in the NameBox to the left of the FormulaBar;
Enter the name as follows...

'<sheetname'!AcctNum

Hit 'Enter'.

...where you would substitute the actual sheetname for <sheetname. For
example, for a sheet named "Account Name" you would type...

'account name'!AcctNum

...into the NameBox. Now your code can ref that cell by name and pull
the respective data from Sheets("Data"). This will obviate the need for
an account#/sheetname list because the account# is defined on each
sheet that will pull data from the monthly data sheet.

It would also be good if the areas to receive data are identical on
each sheet. This, of course, would happen by attrition if all
account/months were consolidated on a single worksheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion