VBA print sheet to text file only works on active sheet

[dappy]

Morning folks,

I wish to copy the contents of a particular sheet to a text file. I have this that works well but, that only works on the active sheet so i have to have the macro button on the sheet that i want to be copied to a text file. is it possible to have the macro on a seperate sheet that looks up the required sheet to copy? i've tried to add worksheet("textOP").range but it makes no difference

iLastRow = Worksheets("textOP").Range("A" & Rows.Count).End(xlUp).Row
iLastCol = Worksheets("textOP").Cells(1, Columns.Count).End(xlToLeft).Column
Open "h:\catm.txt" For Output As #1
For i = 1 To iLastRow
For j = 1 To iLastCol
If j <> iLastCol Then 'keep writing to same line
Print #1, Cells(i, j),
Else 'end the line
Print #1, Cells(i, j)
End If
Next j
Next i
Close #1
Shell "notepad.exe ""h:\catm.txt", vbNormalFocus


any advice much appreciated

thanks in advance

 

[mohadin]

Try
For both Print#1

Print #1, Worksheets("textOP").Cells(i, j),

 

[mohadin]

And
May

Open "h:\catm.txt" For Output As #1
With Sheets("textOP")
    iLastRow = .Range("A" & Rows.Count).End(xlUp).Row
    iLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To iLastRow
        For j = 1 To iLastCol
            If j <> iLastCol Then    'keep writing to same line
                Print #1, .Cells(i, j),
            Else    'end the line
                Print #1, .Cells(i, j)
            End If
        Next j
    Next i
End With
Close #1
Shell "notepad.exe ""h:\catm.txt", vbNormalFocus

 

[dappy]

Perfect, that's really does the job.

thank you so much for a swift resolution.

 

[mohadin]

You are very well come
And thank you for the feedback
Be happy