I have 86 worksheets with data that I need to populate an "upload" sheet. Each worksheet will have its own column. I manually did the first sheet and really dont want to have to do 85 more.. Is there a macro i can use to do this for me? My file exceeds the max limit here, so if anyone needs to see it then I can email it or find another way to upload it.

FNMGR wrote:

I have 86 worksheets with data that I need to populate an "upload"
sheet. Each worksheet will have its own column. I manually did the first
sheet and really dont want to have to do 85 more.. Is there a macro i
can use to do this for me? My file exceeds the max limit here, so if
anyone needs to see it then I can email it or find another way to upload
it.

Shouldn't be a problem. Either upload your worksheet to one of the bazillion
filesharing sites, or just post some sample data -- what the 86 worksheets
look like, what the upload sheet looks like, and what data on the 86 needs to
go to the upload.

--
I'd much prefer hiring someone who nailed it
than someone who took twenty lines to get it wrong.

Hi,

Do you mean 86 worksheets (i.e. tabs in one workbook) or workbooks (i.e. separate Excel files)?

The solution will very much depend on which you mean.

PM me for an email address if so you can send it for me to look at if you like.

S.

There are 86 sheets in 1 book.. i will PM you

Try this..

http://www.4shared.com/file/NbqV8rKj...ions_test.html

Hi,

I am having kind of the same problem, so please could you put the solution in case there is one...but ok i have only 25 workbooks and i need to pollute them in different worksheet...and i do the first myself but is like too much work to do for 50 sheets.

sorry for the interference!

thank you a lot .

I dont really have a solution. I had to manually do the first then i copied the formula as text into the other columns then did a find/replace to relace the "Sheet" name. Then a text to columns to get the text to convert back to general.

You shouldn't have had to do all that stuff with text formats. As long as the formulas were set for the first sheet and references locked so the column/row can be dragged sideways/downward (depending on your setup) you could just use find and replace on each column/row in turn to change the sheet reference to the next and the next. It's a horrid workaround, but probably easier than the alternative if you only had to do it once.

The alternative would be writing the formulas using =INDIRECT() to reference each sheet name with a column/row header. It's easy enough to do if you know how to use it, but without example sheets I couldn't tell you how to set it up.

Hope it's of some help at least.

S.

It kept tying to get me to link to a worksheet when i did the formula then tried to find/replace within the formula. I found it just as easy to create the first set of formulas, set as text and copy all those into my columns then do the find/replace. it worked and only took 10 minutes to do 85 sheets so i wasnt complaining much

Very bizarre. I just tried it on your file and got nothing of the sort.
I've emailed it back to you so you can give it a try. Perhaps you were doing it a different way to me. Not that it matter as you've sorted it now anyway :)

Spencer,

I know indirect formula but is not convienient as you might have a lot of summaries in different ways so it takes time...i don't know if you can create a macro if you do your first template manually...maybe is not possible.

have a look at this thread please just to have an idea!!

http://www.excelbanter.com/showthread.php?t=446032


Thank you