cell alignment

claudehollett

Board Regular
Joined
Dec 11, 2003
Messages
89
I need to cycle through each worksheet in a workbook setting selected cell alignments (verticle & horizontal) on each sheet to match cell alignments for the same cell address in a sheet called "Master". The cell alignment will differ for each cell in the selection. Can you help, please. Claude
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
If either there are no formulas in the cells, or the formulas on the Master are the same as on the corresonding cells on te other worksheets, then you could highlight the cells you need formatting on the Master, then click on the Format Painter, then click on the first cell of the required range on the first worksheet. Repeat the process for each worksheet. Done.
 

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
Here is code that will loop through the worksheets in the active workbook and paste formats...

Code:
Public Sub my_copy_format()
On Error Resume Next

    Const strRange As String = "A1:G10"  'modify range as needed
    Const strMaster As String = "Master" 'name of master worksheet
    
    Application.ScreenUpdating = False
    
    'determine current sheet/selection
    Set curSel = Selection
    Set curWs = ActiveSheet

    'copy formats from master worksheet
    Worksheets(strMaster).Activate
    Range(strRange).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    'loop through each worksheet and paste formats
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> strMaster Then
            ws.Activate
            Set wsSel = Selection
            Range(strRange).Select
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
            wsSel.Select
        End If
    Next ws
    
    'restore workbook to original sheet/selection
    curWs.Activate
    curSel.Select
    Application.ScreenUpdating = True
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,113,862
Messages
5,544,727
Members
410,630
Latest member
JFORTH97
Top