View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
LiAD LiAD is offline
external usenet poster
 
Posts: 386
Default Dynamic copy range

Sorry,

I had forgotten that I have three worksheets that I do not want to copy the
format to. I have sheets a,b,c then sheets 1,2,3. I want to copy from 1 to
2,3 etc....

Do I have to name each sheet I want or how its best to go about this?

Thanks


"Rick Rothstein" wrote:

Here is another way to do what you want...

Sub CopyFormats()
Dim X As Long
For X = 2 To Worksheets.Count
Worksheets(1).Range("P10:AA109").Copy Worksheets(X).Range("P10")
Worksheets(X).Range("P10:AA109").ClearContents
Next
End Sub

This code assumes your "Sheet 1" is the first sheet tab on the left. If that
is not the case, let me know and I'll adjust the code to handle it (but let
me know the exact sheet name though so I can build my code around it). Also
note that I assumed you meant AA109 when you wrote A109).

--
Rick (MVP - Excel)


"LiAD" wrote in message
...
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