Not sure how to replicate cell formats along with cell values

I use the following code to replicate the values of cells in a range in one workbook to the corresponding cells in another workbook:

    ActiveWorkbook.Sheets("Sheet1").Range("D6:K26").Value = _
    ThisWorkbook.Sheets("Sheet1").Range("D6:K26").Value

Fairly straightforward and works just fine in replicating the cell values.

However I also need to replicate the cell colour and font colour attributes for each cell in the range. It wouldn't bother me if it replicated all format attributes, but those are the two that matter most.

Is there a similarly straightforward means to replicate the individual cell's format attributes for the range?

Thanks.
Unfortunately that just wipes out all the content in the range in the original sheet (but leaves the formatting) and doesn't copy anything across to the other sheet (values or formats).

Originally Posted by JapanDave

Try this,

Sub dave()
  Dim wb As Workbook
  
    Set wb = Workbooks("TPS~v1.5.4")
    ClientFileName = "TPS data_" + Sheets("ClientData").Range("Athlete_Name").Value
    
'   open up a new workbook and add sheets for copying data to
    Workbooks.Add
    
     wb.Sheets("Sheet1").Range("D6:K26").Copy ActiveWorkbook.Sheets("Sheet1").Range("D6:K26")

End Sub

Cool, thanks, that works.

It copies values and font formats, but it isn't copying cell colours though.

I think the problem might be due to the colours in the original worksheet being created by conditional formatting.

Which might mean I'll need a different approach to the problem. I have a couple of ideas to try...

Ok, forgive me but my original code operates fine, with cells being copied from one workbook to a different workbook. In my code, ActiveWorkbook and ThisWorkbook do not seem to refer to the same workbook.

For context, the code preceding this activates the first workbook (the one I want to copy from, in this case the workbook called "TPS~v.1.5.4") and then adds a new workbook, the one I want to copy to.

'   set deafult file name as "TPS data_" plus the client's name as shown
'   at cell "Ä2" or use "Athlete_Name" range name in the ClientData sheet of TPS
    Workbooks("TPS~v1.5.4").Activate
    ClientFileName = "TPS data_" + Sheets("ClientData").Range("Athlete_Name").Value
    
'   open up a new workbook and add sheets for copying data to
    Workbooks.Add

After all the copying is done, the code moves onto to save the new workbook, and give user option to rename from a default and choose a directory.

For some reason the code I use replicates the data from one to the other just fine. I don't however know why.