Format Painting

Hello,

Sorry for hogging, just another quick one...

I can find out why I cant do this...

'formats the Reforecast columns to be the same as the Forecast column

Workbooks("103 Reforecasts").Sheets("Reforecast").Columns("B:B").Copy

Workbooks("103 Reforecasts").Sheets("Reforecast").Range(Columns(3), Columns(col)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Workbooks("103 Reforecasts").Sheets("Reforecast").Application.CutCopyMode = False




The debugger doesnt like:

Workbooks("103 Reforecasts").Sheets("Reforecast").Range(Columns(3), Columns(col)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Many thanks

Tom

RE: Format Painting

Hi Stephen,

It says the following...

'Select Method of Range Class Failed' but there are several errors depending what I try in order to fix the problem.

I'll try and explain it further with this example...

This works fine...

Range("BP5:BW5").AutoFill Destination:=Range("BP5:BW9"), Type:=xlFillDefault
Range("BP5:BW9").Select

But I want to call it when that sheet is not the active sheet, so I tried this...

Workbooks("Dist Plan 103").Sheets("Team Attendance").Active
Range("BP5:BW5").AutoFill Destination:=Range("BP5:BW9"), Type:=xlFillDefault
Range("BP5:BW9").Select

This...

Workbooks("Dist Plan 103").Sheets("Team Attendance").Range("BP5:BW5").Select
Selection.AutoFill Destination:=Workbooks("Dist Plan 103").Sheets("Team Attendance").Range("BP5:BW9"), Type:=xlFillDefault
Workbooks("Dist Plan 103").Sheets("Team Attendance").Range("BP5:BW9").Select

and several other attempts and it just doesnt like it when I put the full address of the range, in particular the part about the AutoFill Destination.

Thanks again,

Tom

RE: Format Painting

Thanks Stephen that's great.

Oh just one last thing (I sound like Columbo there!) how would you make your example change the formatting of the Target sheet to the be that of that Team Attendance tab for the ranges you mentioned?

Something like...

Sheets("Target").Range("A1:h3").Format = Sheets("Team Attendance").Range("A1:h3").Format


The drop down list of attributes available (such as .value) doesnt seem to come up when write...

Sheets("Target").Range("A1:h3").

...I want to see if there is a formatting attribute.

Lastly is attribute the right word to use!

Thanks very much for your time

Tom