Worksheet formatting with Macro...
 

TIA for any help.

I think this is a quick and easy question. I just can't find th
answer in my searching (probably cause I am not asking the righ
question!!).

Part of one of my macros, formats 8 worksheet tabs (each with
different name) exactly the same. I essentially copy the appropriat
header range from the master sheet containing all the data and the
paste special to each following sheet.

Here is the formatting code:

Sheets("ALL").Select
Range("A3:J3").Select
Selection.Copy

' I repeat this code for each following sheet with each sheet name
Sheets("Contribs").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone
SkipBlanks:= _
False, transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Operation:=xlNone, _
SkipBlanks:=False, transpose:=False
ActiveWindow.Zoom = 80

Is there an easier way to do this without having to select each shee
and going through this pasting process? I am using the same cells i
every sheet

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=54881

Worksheet formatting with Macro...
 

Sub FormatSheets()
Dim wS As Worksheet

Sheets("ALL").Range("A3:J3").Copy
For Each wS In Worksheets

If UCase(wS.Name) < "ALL" Then
With wS.Range("A3")
.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
End With
End If

Next
Application.CutCopyMode = False

End Sub

--
Cheers
Nigel



"Celt" wrote in message
...

TIA for any help.

I think this is a quick and easy question. I just can't find the
answer in my searching (probably cause I am not asking the right
question!!).

Part of one of my macros, formats 8 worksheet tabs (each with a
different name) exactly the same. I essentially copy the appropriate
header range from the master sheet containing all the data and then
paste special to each following sheet.

Here is the formatting code:

Sheets("ALL").Select
Range("A3:J3").Select
Selection.Copy

' I repeat this code for each following sheet with each sheet name
Sheets("Contribs").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, transpose:=False
ActiveWindow.Zoom = 80

Is there an easier way to do this without having to select each sheet
and going through this pasting process? I am using the same cells in
every sheet.


--
Celt
------------------------------------------------------------------------
Celt's Profile:
http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=548811

Worksheet formatting with Macro...
 

Thanks Nigel!

That works great. Much better than what I originally had.

One follow up question, is there a way to incorporate resizing the
worksheet?

In my original coding, I had "ActiveWindow.Zoom = 80". I tried
dropping that into the code you provided and then realized your code is
not actually "selecting" each worksheet, so I don't think
ActiveWindow.Zoom will work.

Any ideas on that problem?

Thanks again for your help!!!


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=548811

Worksheet formatting with Macro...
 

Nevermind.

I figured it out. This is what I used....

Sheets("ALL").Range("A3:J3").Copy
For Each wS In Worksheets
wS.Select
ActiveWindow.Zoom = 80
If UCase(wS.Name) < "ALL" Then
With wS.Range("A3")
PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, transpose:=False
PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
SkipBlanks:=False, transpose:=False
End With
End If
Next
Application.CutCopyMode = False

Seems to work without any glitches.

Thanks again for your help Nigel!

Cheers
Celt


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=548811

Worksheet formatting with Macro...
 

Glad it works well, I see no problem with it. You might like to add the
final statement to activate the and select a cell on the master sheet
(whatever that might be) with something like

Sheets("ALL").Activate
Range("A1").Select.

Put this after the Application.CutCopyMode = False statement.

--
Cheers
Nigel



"Celt" wrote in message
...

Nevermind.

I figured it out. This is what I used....

Sheets("ALL").Range("A3:J3").Copy
For Each wS In Worksheets
wS.Select
ActiveWindow.Zoom = 80
If UCase(wS.Name) < "ALL" Then
With wS.Range("A3")
PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, transpose:=False
PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
SkipBlanks:=False, transpose:=False
End With
End If
Next
Application.CutCopyMode = False

Seems to work without any glitches.

Thanks again for your help Nigel!

Cheers
Celt


--
Celt
------------------------------------------------------------------------
Celt's Profile:
http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=548811