Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error with macro to name new worksheet with cell from old worksheet | Excel Programming | |||
Help: VBA macro for worksheet formatting | Excel Programming | |||
How? Macro to copy range to new worksheet, name new worksheet, loop | Excel Programming | |||
macro to apply worksheet event to active worksheet | Excel Programming | |||
Record Worksheet Content as Macro and Execute from another Worksheet | Excel Programming |