probably best to have a table with the sheet numbers in it
say its called table1 on ther control sheet

dim cell as range
Sheets("1").Range("P10:AA109").Copy
For Each cell in worksheets("control").Range("Table1").Cells
Worksheets(cell.Value).Range("P10").PasteSpecial xlPasteFormats
Next

"LiAD" wrote:

Yes I want to copy only the format ONLY (specifically only the borders
actually), no formulas or anything else.

The formulas I tried, (was given by you three guys), copied it to all 10
sheets, I only want to copy it to specific sheets with the file. Do I need
therefore to provide a list of the sheet names i wish to use or how do i
limit to which sheets it copies?

Otherwise it is copying from the fourth sheet to the 11th. Sheets 12-15
shoud not be formatted.

Thanks

"Patrick Molloy" wrote:

two replies suggest that you do NOT want to copy the format?

your question indicated that you ONLY wanted to copy the format.

If you want just formula then change XLPasteFormats to xlPasteFormulas

note, more btraodly:
you can combine also:
with Sheets("2").Range("P10")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
end with

"LiAD" wrote:

Afternoon,

I have a file with 10 different sheets in it. I have a macro part of which
is attached below, which copies the format from a set of cells in sheet 1 and
copies them into sheet 2. I need to do this for all 10 sheets - take the
format thats in cells cells P10 to A109 from sheets 1 and copy it to sheet 2,
then sheet 3, sheet 4 etc etc. All copying is from sheet 1.

Is it possible to have one formula that will loop through the sheets without
me having to repeat the same code 10 times?

Thanks

Sheets("1").Range("P10:AA109").Copy
Sheets("2").Range("P10").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Next code would be (exactly the same apart from sheet ref in second line

Sheets("1").Range("P10:AA109").Copy
Sheets("3").Range("P10").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False