Macro to copy the format of a chosen named range to another fixed point

[FrankLinssen]

I have a field called period with the variables 1 to 13. In the file I have a dropdownlist where the user can select a period. Each period is linked to a named range.
Like this:

When the user selected e.g. 6 as the period the range tb_per_202406 is pulled in to a certain cell. I am using the following formula for that: =TAKE(INDIRECT(VLOOKUP(T1,$Q$2:$R$14,2,0)),1000,5)

All this works perfectly. Now the challenge is as follows. Each named range has a certain formatting (also different for each range).
I also want to pull in (read "copy") the formatting of the selected period (= range). But as far as I know Excel cannot do that. So I was thinking of using VBA for this.
Basically what I want is that the macro is copying the format of the chosen range (via the selected period) and then go to a certain cell (named as "startcell") and have the format pasted to that cell?

I hope my question is clear?
PS using Office 365

 

[DanteAmor]

One final question: does something change to the first macro when I move the range Q1:R14 and cell T1 to another sheet (somewhere at the end of my file, which is much bigger than the example-file)?

Nothing changes. You only have to execute the macro on the sheet that contains the period in T1 and the table in Q1:R14

If you want to run the macro on any sheet, then use the following macro, just change "sample" to the name of the sheet that will contain the period in T1 and the table in Q1:R14:

Sub copycolor_v2()
  Dim f As Range
 
  Set f = Sheets("sample").Range("Q2:Q14").Find(Sheets("sample").Range("T1").Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    Application.ScreenUpdating = False
    Range("startcell").Resize(1000, 5).ClearFormats
    Sheets("hardcoded formatted data 13per.").Range(f.Offset(0, 1).Value).Copy
    Range("startcell").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
  End If
End Sub