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
RE: Format Painting
Hi Tom
What error message do you get when you run this?
Thanks
Stephen
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
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").ValueThis will run much more quickly and is more efficient.
Regards
Stephen
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
