Format Painting

Resolved · Medium Priority · Version 2007

Tue 8 Jun 2010, 15:58 Delegate Tom said...

Tom has attended:
Excel VBA Intro Intermediate course
Access Intermediate course

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

Wed 9 Jun 2010, 17:19 Trainer Stephen said...

RE: Format Painting

Hi Tom

What error message do you get when you run this?

Thanks

Stephen

Fri 11 Jun 2010, 14:04 Delegate Tom said...

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

Thu 1 Jul 2010, 09:34 Trainer Stephen said...

RE: Format Painting

Hi Tom

Thanks for update. Apologies for the delay, I have been on leave

Try selecting the sheet before you select or paste to a range on it

e.g.

Sheets("Dist Plan 103").Select
range("A1:d3").Select

You cannot select a range unless you have first selected the sheet.

In principle it is better practice not to use the select command at all, but directly set the value property of the ranges in question

e.g

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

This will run much more quickly and is more efficient.

Regards

Stephen

Thu 1 Jul 2010, 10:55 Delegate Tom said...

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