refreshing a range of tabs.

[bbannock]

Hi everyone, I'm not very VB savy and I'm looking for some help improving some macros that I use. Right now the macro that I use to refresh a group of sheets looks something like the following:
-----------------------------------------------------------
Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal lockFlag As Variant) As Long
___________________________________________________________
Sub RefCompData()
X = EssVRetrieve("sheet 1", Null, 1)
X = EssVRetrieve("sheet 2", Null, 1)
X = EssVRetrieve("sheet 3", Null, 1)
If X = 0 Then
MsgBox ("REFRESH SUCCESSFUL")
Else
MsgBox ("REFRESH FAILED - TRY AGAIN")
End If
Calculate
End Sub
-----------------------------------------------------------

I want to find a way to refresh the range of sheets rather than listing each sheet individually in the macro. As it is now we have to go in and change the macro whenever we add or remove a sheet. If I could right a macro which would recognize the range of sheets rather than each sheet on its own that would be great. Thanks alot everyone.

Brandon

 

[PHV]

Something like this ?
Sub RefCompData()
For Each sh In ActiveWorkBook.Worksheets
X = X + EssVRetrieve(sh.Name, Null, 1)
Next sh
If X = 0 Then
MsgBox ("REFRESH SUCCESSFUL")
Else
MsgBox ("REFRESH FAILED - TRY AGAIN")
End If
Application.Calculate
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bbannock]

That worked like a charm...now how can I do the same with the following code? Thanks for the help.


Sub HIDEROWS()
'


'
Sheets("sheet 1").Select
range("P1").Select
range(Selection, Selection.End(xlDown)).Select
For Each X In Selection
If ActiveCell.Value = "HIDE" Then
Selection.EntireRow.Hidden = True
End If
If ActiveCell.Value = "SHOW" Then
Selection.EntireRow.Hidden = False
End If
ActiveCell.Offset(1, 0).Select
Next X
range("B10").Select

Sheets("sheet 2").Select
range("P1").Select
range(Selection, Selection.End(xlDown)).Select
For Each X In Selection
If ActiveCell.Value = "HIDE" Then
Selection.EntireRow.Hidden = True
End If
If ActiveCell.Value = "SHOW" Then
Selection.EntireRow.Hidden = False
End If
ActiveCell.Offset(1, 0).Select
Next X
range("B10").Select
End Sub

 

[bbannock]

to clarify this code hide zeroed rows on a spread sheet...so it should go to a specific column in each sheet and look at all the values in that column. If the cell reads hide it should hide the row and if it reads show it should leave it showing.

Sorry for any confusion.

Brandon

 

[bbannock]

just want to top this - still trying to figure it out.

thanks,
Brandon