Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Y doens't this work?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 83
Default Y doens't this work?
Sub SetReferences()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
For i = 1 To 1
Cells(i, 2).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!A1"
Cells(i, 3).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!B1"
Next i
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B300"), Type:=xlFillDefault
Range("B1:B300").Select
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C300"), Type:=xlFillDefault
Range("C1:C300").Select
End With
Next ws
End Sub

i'm simply trying to loop through all 52 of my sheets and fill B:C 1-300
with the value.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,501
Default Y doens't this work?
Stephen

I'm unsure what it is you writing to eacg range but this now loops through
each sheet and writes it

Sub SetReferences()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Cells(1, 2).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!A1"
Cells(1, 3).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!B1"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B300"), Type:=xlFillDefault
Range("B1:B300").Select
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C300"), Type:=xlFillDefault
Range("C1:C300").Select
Next ws
End Sub

Mike

"Stephen" wrote:

Sub SetReferences()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
For i = 1 To 1
Cells(i, 2).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!A1"
Cells(i, 3).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!B1"
Next i
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B300"), Type:=xlFillDefault
Range("B1:B300").Select
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C300"), Type:=xlFillDefault
Range("C1:C300").Select
End With
Next ws
End Sub

i'm simply trying to loop through all 52 of my sheets and fill B:C 1-300
with the value.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 29
Default Y doens't this work?
You can probably simplify by not selecting anything ever, but this
makes it work. See comments added.


Sub SetReferences()
Dim ws As Worksheet, i As Integer
For Each ws In ThisWorkbook.Worksheets
With ws
For i = 1 To 1
ws.Cells(i, 2).Value = "='R:\...\...\...\...\...\...\
[S1.CSV]S1'!A1" '<<--reference the ws you want to act upon
ws.Cells(i, 3).Value = "='R:\...\...\...\...\...\...\
[S1.CSV]S1'!B1" '<<--reference the ws you want to act upon
Next i

ws.Activate
'<<Activate the sheet before you select
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B300"),
Type:=xlFillDefault
Range("B1:B300").Select
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C300"),
Type:=xlFillDefault
Range("C1:C300").Select
End With
Next ws

On Dec 19, 11:13 am, Stephen
wrote:
Sub SetReferences()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
For i = 1 To 1
Cells(i, 2).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!A1"
Cells(i, 3).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!B1"
Next i
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B300"), Type:=xlFillDefault
Range("B1:B300").Select
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C300"), Type:=xlFillDefault
Range("C1:C300").Select
End With
Next ws
End Sub

i'm simply trying to loop through all 52 of my sheets and fill B:C 1-300
with the value.



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 83
Default Y doens't this work?
that's stupid of me...

thnka a bunch!

"Mike H" wrote:

Stephen

I'm unsure what it is you writing to eacg range but this now loops through
each sheet and writes it

Sub SetReferences()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Cells(1, 2).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!A1"
Cells(1, 3).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!B1"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B300"), Type:=xlFillDefault
Range("B1:B300").Select
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C300"), Type:=xlFillDefault
Range("C1:C300").Select
Next ws
End Sub

Mike

"Stephen" wrote:

Sub SetReferences()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
For i = 1 To 1
Cells(i, 2).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!A1"
Cells(i, 3).Value = "='R:\...\...\...\...\...\...\[S1.CSV]S1'!B1"
Next i
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B300"), Type:=xlFillDefault
Range("B1:B300").Select
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C300"), Type:=xlFillDefault
Range("C1:C300").Select
End With
Next ws
End Sub

i'm simply trying to loop through all 52 of my sheets and fill B:C 1-300
with the value.


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 to update a column in a work based on another work sheet WickerMan New Users to Excel 1 December 4th 09 12:58 PM
CTRL+spacebar doens't work in Excel 2007 ximee23 Excel Discussion (Misc queries) 3 September 11th 09 09:10 AM
HELP!!! Macro doens't run on clients maschine Michael Excel Programming 3 August 10th 07 09:34 AM
If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? Marc Excel Programming 2 July 12th 06 04:10 AM
Why Excel doens't display all text in a cell Dan New Users to Excel 14 April 22nd 05 02:09 PM


All times are GMT +1. The time now is 04:33 AM.

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