Moving to a new sheet.

  • Thread starter Thread starter Kevin Mulvaney
  • Start date Start date
K

Kevin Mulvaney

I cannot for the life of me figure out how to have a macro perform a function
and then select the next sheet. I can get it to go to a specific sheet,
either by name or index, but I just want to go to the next sheet.
 
You may need to be more specific about what you want to do for the
"function". You may also get a quicker response in the programming group.
Post your code as well.
 
Thanks,

The macro is just a simple print funtion that locks all the data in the
sheet before printing.

Range("B10:B45,E7:F45,H7:N45").Select
Range("H7").Activate
ActiveSheet.Unprotect
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
<--- this is where I want to move to the next sheet -->
 
Try this

Sub test()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim myRange As Range

Set aWS = ActiveSheet
Set aWB = ActiveWorkbook
With aWS
Set myRange = .Range("B10:B45,E7:F45,H7:N45")
.Unprotect
End With

With myRange
.Locked = True
.FormulaHidden = False
End With

aWS.Range("H7").Activate 'Not sure if this is even needed.

aWS.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"

If aWB.Worksheets.Count = aWS.Index Then
aWB.Worksheets(1).Select
Else
aWB.Worksheets(aWS.Index + 1).Select
End If

End Sub
 
Thanks for the codes. Since I am still a bit new with VB I am going to go
with Don's. It's far more simple and I can remember it.
 
Back
Top