Loop through all visible worksheets in excel

[ushasharma84]

hi
I have a code which converts a single worksheet to xml. Now I want that all worksheets data of a workbook to get converted to xml at same time on a single click.
I am pasting the code which I am using

findusedrange subroutine which automatically selects the non empty continuous region of the current/active worksheet
' To automatically select the "REAL"/non empty continuous regions (rows and columns)
Sub FindUsedRange()
Dim LastRow As Long
Dim FirstRow As Long
Dim LastCol As Integer
Dim FirstCol As Integer
' Find the FIRST real row
FirstRow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
' Find the FIRST real column
FirstCol = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
' Find the LAST real row
LastRow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
' Find the LAST real column
LastCol = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
'Select the ACTUAL Used Range as identified by the variables identified above
'MsgBox (FirstRow & "," & LastRow & "," & FirstCol & "," & LastCol)
Dim topCel As Range
Dim bottomCel As Range
Set topCel = Cells(FirstRow, FirstCol)
Set bottomCel = Cells(LastRow, LastCol)
ActiveSheet.Range(topCel, bottomCel).Select
End Sub

Subroutine for writing plain string out a file
Sub sWriteFile(strXML As String, strFullFileName As String)
Dim intFileNum As String
intFileNum = FreeFile
Open strFullFileName For Output As #intFileNum
Print #intFileNum, strXML
Close #intFileNum
End Sub

These subroutines have been called on a button click
Private Sub CmdProcessXML_Click()
Dim strXML As String
If Application.Count(Selection) = 0 Then
FindUsedRange
End If
'fGenerateXML sub routine generates xml from an excel range strXML = fGenerateXML(Selection, "issue")
' sWriteFile strXML, ThisWorkbook.Path & filenameinput
sWriteFile strXML, filenameinput
MsgBox ("Completed. XML Written to " & filenameinput)
Startform.Hide
End Sub

providing a default name to exported xml file and saving in the same directory in which xls file resides

Private Sub UserForm_Initialize()
filenameinput.Text = ThisWorkbook.Path & "\default.xml"
End Sub
:eek:


Thanks in advance

[Bob Phillips]

Just use a simple loop



For Each sh in Activeworkbook.Worksheets
...
Next sh

and replace all reference to Activesheet with sh

[Kenneth Hobs]

As xld said, you should replace activesheet with sh. You can set an input parameter for your routines to use sh.

Replace the MsgBox with your code to run on the visible worksheets.
Sub Test()
Dim sh As Worksheet
For Each sh In Worksheets
If sh.Visible = xlSheetVisible Then
MsgBox sh.Name, vbInformation, "Is Visible"
End If
Next sh
End Sub

Some of your code may need an sh prefix. If you are doing something with the activesheet, you may want to add code to skip it in your loop.
e.g.
Sub FindUsedRange(sh As Worksheet)
Dim LastRow As Long
Dim FirstRow As Long
Dim LastCol As Integer
Dim FirstCol As Integer
' Find the FIRST real row
FirstRow = st.Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row

' Find the FIRST real column
FirstCol = st.Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByColumns).Column

' Find the LAST real row
LastRow = st.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
' Find the LAST real column
LastCol = st.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

'Select the ACTUAL Used Range as identified by the
'variables identified above
'MsgBox (FirstRow & "," & LastRow & "," & FirstCol & "," & LastCol)
Dim topCel As Range
Dim bottomCel As Range

Set topCel = Cells(FirstRow, FirstCol)
Set bottomCel = Cells(LastRow, LastCol)

st.Range(topCel, bottomCel).Select
End Sub