same macro on multiple sheets

A

abreijer1

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
 
D

Don Guillett

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
(e-mail address removed)
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
Click to expand...
 
D

Don Guillett

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
(e-mail address removed)
Don Guillett said:
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
(e-mail address removed)
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
Click to expand...
Click to expand...
 
A

abreijer1

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



hello,
Click to expand...
can someone help me
i was searching for a macro who is repeating my macro on another sheet
Click to expand...
this is what i have , but it works only on de last sheet
Click to expand...
Sub adres()
Click to expand...
Dim sh As Worksheet
For Each sh In Worksheets(Array("Verpand derde 9", "RVS Verp derde"))
Click to expand...
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 -
Click to expand...

- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

thank you very much , it works fine
 
D

Don Guillett

glad to help

--
Don Guillett
SalesAid Software
(e-mail address removed)
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



hello,
Click to expand...
can someone help me
i was searching for a macro who is repeating my macro on another sheet
Click to expand...
this is what i have , but it works only on de last sheet
Click to expand...
Sub adres()
Click to expand...
Dim sh As Worksheet
For Each sh In Worksheets(Array("Verpand derde 9", "RVS Verp derde"))
Click to expand...
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 -
Click to expand...

- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

thank you very much , it works fine
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to copy rows that contain a cell with a certain number 2
criteriarange not equal to 4
Macro that deletes and pastes filterd rows and then some 2
fixing a run-time error'9': Subscript out of range 0
Macro Crashing 3
Complie Syntax Error 6
Vlookup in vba - how to use absolute rows not relative 1
File Name changes with dates and Deleting Rows of Zeros 0

Top