Duplicate contents including formatting

L

Lstallan

New Member
Joined
Apr 27, 2018
Messages
15
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???
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Can't think of anyway to do this just with a formula. Can do it via VBA, what are you looking to do?
 
Upvote 0
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
 
Upvote 0
Can you share a sample with some expected results. Trying to visualize what you are looking for.
 
Upvote 0
the daily sheet looks like this - I've formatted 2 names for examples...

[TABLE="width: 259"]
<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>[TR]
[TD="class: xl69, width: 343, bgcolor: transparent, colspan: 12"]Dispatch Team Leader[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent, colspan: 3"]0600-1800[/TD]
[TD="class: xl87, bgcolor: transparent, colspan: 9"]C IQBAL[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent, colspan: 3"]0600-1800[/TD]
[TD="class: xl81, bgcolor: transparent, colspan: 9"]T MCEWAN[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent, colspan: 3"]0600-1800[/TD]
[TD="class: xl78, bgcolor: transparent, colspan: 9"]C CAMINO[/TD]
[/TR]
[TR]
[TD="class: xl83, bgcolor: transparent, colspan: 3"]0900-1500[/TD]
[TD="class: xl85, bgcolor: transparent, colspan: 9"]T STRUTTON [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, colspan: 12"]Dispatchers[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent, colspan: 3"]0630-1830[/TD]
[TD="class: xl73, bgcolor: transparent, colspan: 9"]A KARAMBAKUWA[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent, colspan: 3"]0630-1830[/TD]
[TD="class: xl73, bgcolor: transparent, colspan: 9"]A MANDAL[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent, colspan: 3"]0630-1830[/TD]
[TD="class: xl73, bgcolor: transparent, colspan: 9"]R MUHAMMED[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent, colspan: 3"]0630-1830[/TD]
[TD="class: xl73, bgcolor: transparent, colspan: 9"]C MASTRANDREA[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent, colspan: 3"]0800-1330[/TD]
[TD="class: xl73, bgcolor: transparent, colspan: 9"]D CLARKE[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent, colspan: 3"] [/TD]
[TD="class: xl76, bgcolor: transparent, colspan: 9"] [/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 259"]
<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>[TR]
[TD="class: xl68, width: 172, bgcolor: transparent"]DTL[/TD]
[TD="class: xl69, width: 173, bgcolor: transparent"]C IQBAL[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]DTL[/TD]
[TD="class: xl71, bgcolor: white"]T MCEWAN[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]DTL[/TD]
[TD="class: xl73, bgcolor: white"]C CAMINO[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]0900-1500[/TD]
[TD="class: xl73, bgcolor: white"]T STRUTTON [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Dispatcher[/TD]
[TD="class: xl69, bgcolor: transparent"]A KARAMBAKUWA[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Dispatcher[/TD]
[TD="class: xl72, bgcolor: transparent"]A MANDAL[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Dispatcher[/TD]
[TD="class: xl71, bgcolor: white"]R MUHAMMED[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]Dispatcher[/TD]
[TD="class: xl71, bgcolor: white"]C MASTRANDREA[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]0800-1330[/TD]
[TD="class: xl71, bgcolor: white"]D CLARKE[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]0[/TD]
[TD="class: xl71, bgcolor: white"] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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?
 
Last edited:
Upvote 0
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

Code: 
' 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
 
Upvote 0
Give this a go.
Code: 
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
 
Upvote 0
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??
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
278
Madam_Xavier
M
O
  • Question Question
Highlighting different sets of duplicates with different colours/ symbols; but it has to be more than 3
Replies
1
Views
461
Orkochu
O
J
  • Question Question
Conditional formatting based on value of another cell
Replies
3
Views
741
etaf
etaf
D
  • Solved
Negative time hh:mm entry and calculations
Replies
3
Views
275
Joe4
Joe4
R
  • Question Question
2 column lookup formula to reference new data
Replies
1
Views
469
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top