Can't save cell values to text file

[Mika]

Const SaveTheseCells As String = "M4:O4;M6:O6;M8:O8;M10:O10;M12:O12"

Sub SaveToFile()
Dim MySheet As Worksheet

For Each MySheet In ActiveWorkbook.Worksheets
Select Case MySheet.CodeName
Case "SpecialSheet":
Dim SheetToFile As Worksheet
Set SheetToFile = MySheet
End Select
Next MySheet

Set MySheet = Nothing

Dim FileName As String
FileName = "C:\test.txt"

FileNum = FreeFile

Open FileName For Output As #FileNum
Print #FileNum, "start"

SheetToFile.Range(SaveTheseCells).Select

Dim cell As Range

'This does not save the values of M4, N4, O4, etc.
'What is wrong with this?

For Each cell in Selection.Cells
Print #FileNum, cell.Text
Next cell

Print #FileNum, "end"
Close #FileNum

Set SheetToFile = Nothing
End Sub

 

[Dave Peterson]

I think you have a couple of problems.

SaveTheseCells needs to be delimited by commas. And if the SpecialSheet isn't
the activesheet, then you'll have trouble selecting a range on that sheet.

But this worked ok for me:

Option Explicit
Const SaveTheseCells As String = "M4:O4,M6:O6,M8:O8,M10:O10,M12:O12"
Sub SaveToFile()
Dim MySheet As Worksheet
Dim FileNum As Long
Dim myRng As Range

For Each MySheet In ActiveWorkbook.Worksheets
Select Case MySheet.CodeName
Case "SpecialSheet":
Dim SheetToFile As Worksheet
Set SheetToFile = MySheet
Exit For
End Select
Next MySheet

Set MySheet = Nothing

Dim FileName As String
FileName = "C:\test.txt"

FileNum = FreeFile

Open FileName For Output As #FileNum
Print #FileNum, "start"

Set myRng = SheetToFile.Range(SaveTheseCells)

Dim cell As Range

'This does not save the values of M4, N4, O4, etc.
'What is wrong with this?

For Each cell In myRng.Cells
Print #FileNum, cell.Text
Next cell

Print #FileNum, "end"
Close #FileNum

Set SheetToFile = Nothing
End Sub


======
Just a curiosity question...

I try to put all my Dim's at the top of the code. I find it easier to locate
them later if I need to--and it makes it easier not to duplicate the
declaration.

Is there a reason you sprinkle your Dim's throughout the code?

 

[Mika]

I think you have a couple of problems.
But this worked ok for me:

Thanks, Dave!

Mika