EXCEL VBA

Hello,
can you please help,
I'm trying to use below code to get  each sheet saved.
I get the sheets saved, but each sheet has the information from previous sheet as well.

meaning,
sheet 1 is ok,
sheet 2, it is saving sheet 1 range + sheet 2 range
sheet 3 is saving sheet 1 range + sheet 2 range + sheet 3 range
and so on.

I need each sheet with only the range in the sheet.

Sub TEST()
Dim wshH As Worksheet
    Dim c As Range, r As Range
    Dim output As String
    Dim LastRowW As Long
   
For Each wshH In Worksheets
If wshH.Name <> "58" And wshH.Name <> "1" Then
wshH.Select
    LastRowW = Range("A" & Rows.Count).End(xlUp).Row
    With Range("A2:A" & LastRowW).Select

For Each r In Range("A2:A" & LastRowW).Rows

        For Each c In r.Cells
            output = output & "'" & c.Value & "',"
        Next c
        output = output '& vbNewLine
Next r
    Open "C:\Users\Wassim\Desktop\" & wshH.Name & ".txt" For Output As #1
    Print #1, output
    Close
End With

End If
Next wshH
End Sub

thank you
W.E.BAsked:
Who is Participating?
 
GrahamSkanConnect With a Mentor RetiredCommented:
You are not resetting the 'output' string for reach sheet. Try this:
Sub TEST()
    Dim wshH As Worksheet
    Dim c As Range, r As Range
    Dim output As String
    Dim LastRowW As Long
   
    For Each wshH In Worksheets
        output = "" '<---- set string to empty
        If wshH.Name <> "58" And wshH.Name <> "1" Then
            wshH.Select
            LastRowW = Range("A" & Rows.Count).End(xlUp).Row
            With Range("A2:A" & LastRowW).Select
                For Each r In Range("A2:A" & LastRowW).Rows
                    For Each c In r.Cells
                        output = output & "'" & c.Value & "',"
                    Next c
                    output = output '& vbNewLine
                Next r
                Open "C:\Users\Wassim\Desktop\" & wshH.Name & ".txt" For Output As #1
                Print #1, output
                Close
            End With
        End If
    Next wshH
End Sub

Open in new window

0
 
W.E.BAuthor Commented:
Great
thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.