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

Sub format_inte_pastespecialformat()



'Sheets("WORKINGS").Range("H2:V2").copy

'Sheets("consolidation DATA").Range("H2:V2").PasteSpecial Paste:=xlPasteFormats

End Sub

Sub format_inte_pastespecialformat()



Sheets("WORKINGS").Range("H2:V2").Copy

Sheets("consolidation DATA").Range("G1").Resize(Range("G" & Rows.Count).End(xlUp).Row).PasteSpecial Paste:=xlPasteFormats

End Sub

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

Sub format_inte_pastespecialformat()

Dim LR As Long, LC As Long
LR = Sheets("consolidation DATA").Range("G" & Rows.Count).End(xlUp).Row
LC = Sheets("consolidation DATA").Cells(1, Columns.Count).End(xlToLeft).Column
Sheets("WORKINGS").Range("H2:V2").Copy

Sheets("consolidation DATA").Range("G1").Resize(LR, LC).PasteSpecial Paste:=xlPasteFormats

End Sub

Sub format_inte_pastespecialformatAm()

Dim LC As Long
LC = Sheets("consolidation DATA").Cells(1, Columns.Count).End(xlToLeft).Column
Sheets("WORKINGS").Range("H2:V2").Copy

Sheets("consolidation DATA").Range("G1").Resize(, LC).PasteSpecial Paste:=xlPasteFormats

End Sub

Code not giving expected results

See condensed sample with expected results sheets

This might work on the actual data.

Sub format_inte_pastespecialformat()

Dim LR As Long, LC As Long, ms As Worksheet

Set ms = Sheets("consolidation DATA")

With ms

    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Sheets("WORKINGS").Range("B2:P2").Copy
    
    .Range("B2:P2").Resize(LR, LC).PasteSpecial Paste:=xlPasteFormats

End With

End Sub

And this works on the sample provided

Sub format_inte_pastespecialformatnew()

Dim LR As Long, LC As Long, ms As Worksheet

Set ms = Sheets("EXPECTED RESULTS ")

With ms

    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Sheets("Consolidated").Range("B2:P2").Copy
    
    .Range("B2:P2").Resize(LR, LC).PasteSpecial Paste:=xlPasteFormats
    
    End With

End Sub

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

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

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

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

Sub format_inte_pastespecialformat()

Dim LR As Long, LC As Long, ms As Worksheet

Set ms = Sheets("consolidation DATA")

With ms

    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Sheets("WORKINGS").Range("B2:Q2").Copy
    
    .Range("B2").Resize(LR, LC).PasteSpecial Paste:=xlPasteFormats

End With

End Sub

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.