Duplicate contents including formatting

[Lstallan]

Help needed with duplicating formatting (not conditional)

At the moment a daily worksheet is completed which has some basic font colour formatting in it, but there is no conditional formatting I can use. It is manually formatted.

I want to duplicate the daily sheets to a different layout on a weekly sheet. I'm doing this using:
=IF(Wednesday!A7="0600-1800","DTL",Wednesday!A7)

This doesn't copy the formatting which is manually added on the "Wednesday" sheet, to the weekly sheet which contains the =IF argument remains without formatting.

Any ideas???

 

[iggy_]

Can't think of anyway to do this just with a formula. Can do it via VBA, what are you looking to do?

 

[Lstallan]

VBA would be fine.

At present the administrator who creates the rotas makes a different sheet for each day of the week. I've been asked to recreate the same data in a week to view format (the admin doesn't want to do this herself - too much work apparently)

For staff who are covering relief shifts or overtime, their hours are shown in a different colour. I've already done all the formulas to recreate the data on the weekly sheet, but need to recreate the same formatting too ie overtime shifts in red bold font and relief in amber bold. So if there is some VBA I can add to the weekly sheet to pick up the formatting in the original cells, that would be ideal

 

[iggy_]

Can you share a sample with some expected results. Trying to visualize what you are looking for.

 

[Lstallan]

the daily sheet looks like this - I've formatted 2 names for examples...

Dispatch Team Leader
0600-1800	C IQBAL
0600-1800	T MCEWAN
0600-1800	C CAMINO
0900-1500	T STRUTTON
Dispatchers
0630-1830	A KARAMBAKUWA
0630-1830	A MANDAL
0630-1830	R MUHAMMED
0630-1830	C MASTRANDREA
0800-1330	D CLARKE

<colgroup><col width="26" style="width: 20pt; mso-width-source: userset; mso-width-alt: 950;" span="2"> <col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1389;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="28" style="width: 21pt; mso-width-source: userset; mso-width-alt: 1024;"> <col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;"> <col width="28" style="width: 21pt; mso-width-source: userset; mso-width-alt: 1024;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="28" style="width: 21pt; mso-width-source: userset; mso-width-alt: 1024;"> <col width="30" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1097;"> <tbody> </tbody>

The weekly sheet references the contents of the daily sheet, but I need to copy the formatting of the red text and green text

DTL	C IQBAL
DTL	T MCEWAN
DTL	C CAMINO
0900-1500	T STRUTTON
Dispatcher	A KARAMBAKUWA
Dispatcher	A MANDAL
Dispatcher	R MUHAMMED
Dispatcher	C MASTRANDREA
0800-1330	D CLARKE
0

<colgroup><col width="172" style="width: 129pt; mso-width-source: userset; mso-width-alt: 6290;"> <col width="173" style="width: 130pt; mso-width-source: userset; mso-width-alt: 6326;"> <tbody> </tbody>

 

[Lstallan]

Due to the VERY tight security on the work pc I'm unable to upload a screenshot and forum rules prevent me from uploading data from the sheet.

In the work book there are originally 7 separate sheets (1 for each day of the week) with the shift time in cell A1 - A16 and the staff name in cell D7 - D16 (data laid out down the sheet)
The staff who are on their normal rota schedule will just be in ordinary black text. If they have volunteered for an overtime shift their name will be in red and bold. If they are working a floating relief shift their name will appear in green and bold.

I have created an additional sheet which combines the data from each of the 7 daily sheets into one "week to view" with layout which runs each day across the sheet. Using formula I can reference the data and display it from the day sheet on my new week sheet, but all names appear in black. the red bold and green bold is not copied (obviously), but I still need to format the staff names on the week sheet the same as they are formatted on the day sheet.

Hope this helps to explain?

 

[Lstallan]

update:
I have code which I'm using, however there a 2 problems:

1 - it's not copying the formatting from the Wednesday Sheet
2 - I ONLY want to copy the font format, not all formatting.

Any tips/suggestions greatly appreciated as always

' copyformats Macro
'WEDNESDAY
'  Day Dtl
    Sheets("Wednesday").Select
    Range("D7:L10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("D3:D6").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'  Day Dispatchers
    Sheets("Wednesday").Select
    Range("D12:L17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("D7:D12").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'  Day CH
    Sheets("Wednesday").Select
    Range("D29:L39").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("F3:F12").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    '  Night Dtl
    Sheets("Wednesday").Select
    Range("P7:X10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("H3:H6").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'  Night Dispatchers
    Sheets("Wednesday").Select
    Range("P12:X17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("H7:H12").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'  Night CH
    Sheets("Wednesday").Select
    Range("P29:X39").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("J3:J12").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    
'THURSDAY
'  Day Dtl
    Sheets("Thursday").Select
    Range("D7:L10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("D15:D18").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'  Day Dispatchers
    Sheets("Thursday").Select
    Range("D12:L17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("D19:D24").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'  Day CH
    Sheets("Thursday").Select
    Range("D29:L39").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("F15:F24").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    '  Night Dtl
    Sheets("Thursday").Select
    Range("P7:X10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("H15:H18").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'  Night Dispatchers
    Sheets("Thursday").Select
    Range("P12:X17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("H19:H24").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'  Night CH
    Sheets("Thursday").Select
    Range("P29:X39").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Week").Select
    Range("J15:J24").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

 

[Lstallan]

Does anybody have any thoughts/suggestions on this?

 

[Fluff]

Give this a go.

Sub CopyData()
   Dim Ws As Worksheet
   Dim Mws As Worksheet
   Dim Ar1 As Areas
   Dim Ar2 As Areas
   Dim i As Long, j As Long
   
   Set Mws = Sheets("Week")
   
   For Each Ws In Worksheets
      Set Ar1 = Ws.Range("D7:D10, D12:D17, D29:D39, P7:P10, P12:P17, P29:P39").Areas
      Select Case Ws.Name
         Case "Wednesday"
            Set Ar2 = Mws.Range("D3:D6, D7:D12, F3:F13, H3:H6, H7:H12, J3:J13").Areas
         Case "Thursday"
            Set Ar2 = Mws.Range("D15:D18, D19:D24, F15:F25, H15:H18, H19:H24, J15:J25").Areas
      End Select
      If Not Ar2 Is Nothing Then
         For i = 1 To Ar2.Count
            Ar2(i).Value = Ar1(i).Value
            For j = 1 To Ar2(i).Count
               Ar2(i)(j).Font.Color = Ar1(i)(j).Font.Color
               Ar2(i)(j).Font.Bold = Ar1(i)(j).Font.Bold
            Next j
         Next i
      End If
      Set Ar1 = Nothing
      Set Ar2 = Nothing
   Next Ws
End Sub

As your ranges don't match up I've made a few guesses

 

[Lstallan]

There are 6 ranges in the originating sheet which need the formatting copying separately to the ranges on the destination sheet.
........................Origin range........... Destination
DTL Day ......... (B7:B10)................. (D3:D6)
Dispatch Day.... (B12:B17)............... (D7:D12)
CH Day............ (B29:B39)................ (F3:F13)
DTL Night......... (D7:D10)................ (H3:H6)
Dispatch Night...(D12:D17)............... (H7:H12)
CH Night...........(D29:D39)............... (J3:J13)

This is an example for just one day - Origin ranges are on sheet "Wednesday", destination ranges are on sheet "Week". Without posting all the ranges separately, this procedure will need to be repeated for each day of the week to the "Week" sheet with a different layout to the daily sheet (eg all of the Wednesday data is displayed on the week sheet A2:J13, Thursday is displayed on week sheet A14:J25 and so on for each day)
The data is displayed in a different layout on the week sheet to the daily sheet (hence the 6 distinct ranges)
Hope this clarifies??