Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > same macro on multiple sheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default same macro on multiple sheets
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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default 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





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default same macro on multiple sheets
glad to help

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro for summarizing multiple sheets Validlyte Excel Worksheet Functions 1 February 19th 10 07:33 PM
Check Box Macro for multiple sheets Kbernbr New Users to Excel 1 August 26th 08 01:23 AM
Macro to protect multiple sheets? Stilla Excel Worksheet Functions 12 March 5th 07 07:03 PM
Macro to Rename Multiple Sheets Alan Excel Programming 1 January 9th 04 04:38 PM
Macro For Filtering on Multiple Sheets Rick[_13_] Excel Programming 0 August 25th 03 02:40 AM


All times are GMT +1. The time now is 11:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement