same macro on multiple sheets
On 4 feb, 20:39, "Don Guillett" wrote:
try this idea instead. Notice the placement of the dots . and the
autofill of the entire range.
For Each sh In Worksheets(Array("Verpand derde 9", "RVS Verp derde"))
with sh
.Range("AN2").FormulaR1C1 =
"=VLOOKUP(RC[-1],Blad17!C[-39]:C[-38],2,false)
.Range("AO2").FormulaR1C1 =
"=VLOOKUP(RC[-2],Blad17!C[-40]:C[-38],3,false)
'etc
.range("ao2:an2").AutoFill Destination:=.Range("Ao2:Au200")
next sh
Don Guillett
SalesAid Software
wrote in message
ups.com...
hello,
can someone help me
i was searching for a macro who is repeating my macro on another sheet
this is what i have , but it works only on de last sheet
Sub adres()
Dim sh As Worksheet
For Each sh In Worksheets(Array("Verpand derde 9", "RVS Verp derde"))
Range("AN2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Blad17!C[-39]:C[-38],
2,FALSE)"
Selection.AutoFill Destination:=Range("AN2:AN200"),
Type:=xlFillDefault
Range("AN2:AN200").Select
Range("AO2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Blad17!C[-40]:C[-38],
3,FALSE)"
Selection.AutoFill Destination:=Range("AO2:AO200"),
Type:=xlFillDefault
Range("AO2:AO200").Select
Range("AP2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Blad17!C[-41]:C[-38],
4,FALSE)"
Selection.AutoFill Destination:=Range("AP2:AP200"),
Type:=xlFillDefault
Range("AP2:AP200").Select
Range("AQ2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Blad17!C[-42]:C[-38],
5,FALSE)"
Selection.AutoFill Destination:=Range("AQ2:AQ200"),
Type:=xlFillDefault
Range("AQ2:AQ200").Select
Range("AR2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Blad17!C[-43]:C[-38],
6,FALSE)"
Selection.AutoFill Destination:=Range("AR2:AR200"),
Type:=xlFillDefault
Range("AR2:AR200").Select
Range("AS2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],Blad17!C[-44]:C[-38],
7,FALSE)"
Selection.AutoFill Destination:=Range("AS2:AS200"),
Type:=xlFillDefault
Range("AS2:AS200").Select
Range("AT2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],Blad17!C[-45]:C[-38],
8,FALSE)"
Selection.AutoFill Destination:=Range("AT2:AT200"),
Type:=xlFillDefault
Range("AT2:AT200").Select
Range("AU2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],Blad17!C[-46]:C[-38],
9,FALSE)"
Selection.AutoFill Destination:=Range("AU2:AU200"),
Type:=xlFillDefault
Range("AU2:AU200").Select
Next sh
End Sub- Tekst uit oorspronkelijk bericht niet weergeven -
- Tekst uit oorspronkelijk bericht weergeven -
thank you very much , it works fine
|