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:
| Period | Range |
| 1 | tb_per_202401 |
| 2 | tb_per_202402 |
| 3 | tb_per_202403 |
| 4 | tb_per_202404 |
| 5 | tb_per_202405 |
| 6 | tb_per_202406 |
| 7 | tb_per_202407 |
| 8 | tb_per_202408 |
| 9 | tb_per_202409 |
| 10 | tb_per_202410 |
| 11 | tb_per_202411 |
| 12 | tb_per_202412 |
| 13 | tb_per_202413 |
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
I wonder if there is any logic to formatting each named range differently. Would these be set 'at will' and different every time or are they 'predictable' for each period? That is, period 1 is always bold red, period two is italic green etc.
If so, then you could set-up a conditional format (CF) for "the that start cell" based on the period chosen in T1. Such a CF rules would look like this:
=$T$1=1
=$T$1=2
up to ....
=$T$1=13
Set the format for each period number and apply it to the "start cell"
Having said that I take an educated guess by assuming you are dealing with trial balance reports for 13 periods in a year. Have you considered compiling one transaction table (i.e. journal entries including the opening balances for the year) and summarize that one table by account and based on the period selected by the user? If this is not feasible, that's OK. Just wanted to mention it.
Hi Riny,
No these are not trial balances.
Actually each ranges is a list of recipies and in each recipy there is weight, price and some more stuff and after a number of lines also a total for the recipy. That a blank line and again a recipy and so on and on. Each period is therefore different.
I have one tab which shows max 13 ranges, 1 per period. During the year I am filling another range as soon as the list of recipies is available (hardcopy > paste values plus formats) until I have 13 completed ranges around beginning of Dec. This data I am using in another file. Based on the period selected in this last file I am pulling in the related range for that period (which is sitting in the same file on another sheet) through that Take formula.
The only thing I am struggling with is to also copy over the format for that.
Now I can make 13 macro's; 1 for each range to fo to that range, copy it, go back to "startcell" and paste format.
But I am looking for just 1 macro which somehow picks up the chosen period and then copy that chosen period, go to the startcell and paste as format.
Clear?
I apologise for having jumped to conclusions regarding the purpose of your date. As an accountant I naturally associated "tb" and "13 periods" to and accounting application.
Your explanations makes it clearer, though I do not touch VBA as I'm simply not good enough at it. Perhaps someone else.
A macro wouldn't be that complicated - though I suspect you might be able to get the result you need just using something like the camera tool - but it would help to have a sample workbook for testing. Ideally in the same layout as your actual workbook, though the data can be anonymised as needed.
Thanks,
I got the answer in the meantime via another forum where I posted earlier.
Sub copycolor_v2()
Dim f As Range
Set f = Range("Q2:Q14").Find(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
Range("T1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
End Sub
How can I close this item? Do I need to close this item?
Regards
Consider it closed, but you should have informed us about cross-posting in your first post.
