Menu

same macro on multiple sheets

Search this Thread

#1

Report Post

Posted to microsoft.public.excel.programming

Don Guillett Don Guillett is offline

Don Guillett is offline

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 Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded 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 03:23 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement