assistance on paste special formats vba code

Copying formats from sheets workings (template) to consolidation data sheet in same range as per code is ok.as below

However how to paste specials format only from RANGE (h2:v2) to last data row in sheet consolidation data

there is data from g1 still to last data row

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi AB33

Not giving expected results the actual code highlights in green columns G

As per explanation and jpegs

as per jpeg expected results of consolidation data pastespecials format only

It is a guess work. Which columns and rows do you want to format in sheet consolidation?

It could be one of these

Please Login or Register  to view this content.

Code not giving expected results

See condensed sample with expected results sheets

This might work on the actual data.

Please Login or Register  to view this content.

And this works on the sample provided

Please Login or Register  to view this content.

Sintek

no,The file itself is big spreadsheet, what I was trying to explain instead of column (H2:V2).I condensed a sample starting from columns A with data instead of G
and the range paste special format is the same just columns have changed , otherwise if the correct coding was working, would change the range

AB33

nearly on the right track, just at columns Q and row 8 shows a discrepancy on actual macro .
On the expected sheets results there are formula in the columns yellow and green cells , some format cells green and yellow has non contigious cells formulas .
my apology for this insight

Hi Jean,
The code only copies Range("B2:P2"). If you want to expand.

Range("B2:Q2").
Try this one

Please Login or Register  to view this content.

Okay, I see it is colouring row 8 as well, please change this line

LR = .Range("A" & .Rows.Count).End(xlUp).Row

INTO

LR = .Range("A" & .Rows.Count).End(xlUp).Row - 1

Hi Jean,
It was my mistake.
What is important is the copy range.

Sheets("WORKINGS").Range("B2:P2").Copy

means columns B: P and when you paste it, you only need the starting range, not the whole range.
It does not format Column Q as there is no format in column Q. If you look at the data, the range stretches to column P, not Q, but if your format column Q and paste it, you will see the result.
B- P- there are 15 columns, so when you paste it, it paste it on 15 columns.