Trouble making another worksheet active

[Todd K.]

I have a workbook with 10 worksheets, and I want a button on the last one to
create text files of the date in each of the other 9. I made a command
button on the last page ("Summary") and I want the button to perform this
operation on the first page ("Header"). Here's the code:

Private Sub BtnExport_Click()
' This saves the data from the Header worksheet as a .txt file called
"Header.txt"

' Declare variables
Dim Col As Integer
Dim outputline As String
Dim fs As Object
Dim a As Object

' Create a new text file
Set fs = CreateObject("scripting.filesystemobject")
Set a = fs.createtextfile("c:\header.txt", True)

' Set the Header worksheet as the active worksheet
Worksheets("Header").Activate

For Col = 1 To 9
If Len(outputline) > 0 Then
outputline = outputline & ";" & Cells(3, Col)
Else
outputline = Cells(3, Col)
End If
Next Col

a.writeline (outputline)

End Sub

The function works great, but it outputs the data from the "Summary"
worksheet instead of the "Header" worksheet. What am I doing wrong?

 

[Gary''s Student]

Hi Todd:

I got your macro to work by putting it in a standard module, making it
public, and having the button call it>

 

[Todd K.]

Well this falls under the "why didn't I think of that" category. Great idea
and quick response, thanks!

 

[Gary''s Student]

You are very welcome.