loop through worksheets

[Newbie2223]

Hi All

I found this piece of code that works on the Active Sheet
The problem I have is that my excel workbook was create by someone else
and populates over 5 sheets..once I open the workbook.

I would like to build a macro to auto copy sheet 3, 4 and 5 only
How or what do I have to add to get the below code to
loop thru sheet 3, 4 and 5


Sub SaveAsPipeDelimited()
Dim r, c As Integer
Close
Open "C:\Just_Text.txt" For Append As #1
With ActiveSheet.UsedRange
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count - 1
Print #1, .Cells(r, c); "|";
Next c
Print #1, .Cells(r, .Columns.Count)
Next r
End With
Close #1
End Sub

Any help is greatly appreciated

Thanks Newbie2223

 

[PHV]

Something like this ?

Sub SaveAsPipeDelimited()
Dim r As Long, c As Integer, i As Integer
Open "C:\Just_Text.txt" For Append As #1
For i = 3 To 5
  With Worksheets(i).UsedRange
    For r = 1 To .Rows.Count
      For c = 1 To .Columns.Count - 1
        Print #1, .Cells(r, c); "|";
      Next c
      Print #1, .Cells(r, .Columns.Count)
    Next r
  End With
Next i
Close #1
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Newbie2223]

Hi PHV

Thanks for your reply.

I tried the code .however I get

RUN TIME ERROR 9

Substring out of range

When I run it

It does write worksheet 3 to the .txt file...but not 1 and 2

 

[PHV]

You said sheet 3, 4 and 5 !
so, replace this:
For i = 3 To 5
with this:
For i = 1 To 3

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Newbie2223]

Hi PHV

Don't I fell like a idiot..I was testing from home....you are 100% correct.

Thanks so much for your help

Cheers

Newbie2223