macro to run through all worksheets

**jw01** · 12-12-2010, 03:58 AM

hello

seeking some help from the pros regarding a macro that can be copy/paste values from column "B-X", across ROW 2, into the 25+ worksheets that i have in my file?

each worksheet contains the city code, i.e. Miami-Tampa, NY-Newark etc up to a total of 30. this is my master file, which gathers the info. from other files and puts them onto one sheet. so each week (since this is a weekly opex i do), i have to manually copy/paste the values from column B-X and row 2-52 (row 2 = week 2 in the year, to a total of 52 rows = 52 weeks).

also, i was hoping once the macro is run across, if it can alter the change the name of the file it's gather the information from
-i.e. if i copied values from b-x for Miami-tampa, if i run the macro and it copy/paste the values to another worksheet i.e. NY-Newark, can it find/replace the file name as well? below is the formula that is contained in each cell (this is an example from cell "H":

='C:\Users\PK\Desktop\PK Work\Archive\P10W1\[Miami-Tampa.xls]Ghost Summary'!$H$21

so basically, i need the above formula, to be pasted onto the 25+ worksheets and do a find/replace accorindly to the worksheet (tab) name? pls note the "H" is for column H"....and i need the formula from column b-x to be pasted across to each worksheet. please help, thank you.

**royUK** · 12-12-2010, 04:42 AM

Why have separate sheets for each group? It's much more efficient to have one sheet with an identifying column

**jw01** · 12-12-2010, 01:58 PM

hello

i have attached a sample workbook.

in the first worksheet, i have highlighted the line in "yellow" that needs to be copy/pasted (paste special formulas) into the other worksheets.
-pls note once u copy paste the formulas onto the other worksheet, i need to run a find a replace for columns "b-x" and in this instance row 46 and 68 to match the city name in the formula.
-in this sample, i have trimmed it down to a few regions, but i really have a total of 30 so it is quite manually so if it can processed automatically, that would be awesome. pls help and thx you.
-it will prompt to search for the file folder, for this particular instance, i usually hit "escape"

i hope someone can guide with a formula that does the copy paste in the corresponding row for each sheet and then does the find/replace function as well. pls and thxs