ExcelBanter - same macro on multiple sheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - same macro on multiple sheets (https://www.excelbanter.com/excel-programming/382563-same-macro-multiple-sheets.html)

[email protected]

same macro on multiple sheets

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

Don Guillett

same macro on multiple sheets

Don Guillett

same macro on multiple sheets

Correct for word wrap and fix the source 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:aU2").AutoFill Destination:=.Range("Ao2:Au200")
next sh

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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

[email protected]

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

Don Guillett

same macro on multiple sheets

All times are GMT +1. The time now is 12:43 PM.