Y doens't this work?

S

Stephen

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.
 
M

Mike H

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
 
C

chip.gorman

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
 
S

Stephen

that's stupid of me...

thnka a bunch!

Mike H said:
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 said:
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.
Click to expand...
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

I have to run macro twice to work? Why 6
DECLARE VARIABLES PROBLEM 7
For Each ws autofill 1
Complie Syntax Error 6
Autofill variable range 2
Some changes in a Macro .. 4
macro hangs if no data is found 6
lookup returns zero or the wrong number 1

Top