How to go to the next sheet using a Macro
Category: [Excel] Demo Available
How to go to the next sheet using a Macro
Requirement:
The user is having an issue with the code below. When the user runs it, instead of running it in all the 20 excel sheets of my file, the code runs 20 times in the active sheet.
What is the issue?
Sub Macro2()
'
' Macro2 Macro
'
Dim Sh As Worksheet ' coloca SH como Worksheet
For Each Sh In ThisWorkbook.Worksheets
Rows("18:18").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B18").Select
ActiveCell.FormulaR1C1 = "FEVEREIRO 18"
Range("B18").Select
Selection.Copy
Range("K18").Select
ActiveSheet.Paste
Range("E17:G17").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("E17:G18"), Type:=xlFillDefault
Range("E17:G18").Select
Range("N17:P17").Select
Selection.AutoFill Destination:=Range("N17:P18"), Type:=xlFillDefault
Range("N17:P18").Select
Range("C19").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-14]C:R[-1]C)"
Range("C19").Select
Selection.Copy
Range("D19").Select
ActiveSheet.Paste
Range("L19").Select
ActiveSheet.Paste
Range("M19").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-3
Next Sh
End Sub
Solution:
You need to refer to the Sh variable in your loop, otherwise only the active sheet will be affected. Also you can and should remove Selects from your code.
Sub Macro2()
Dim Sh As Worksheet ' coloca SH como Worksheet
For Each Sh In ThisWorkbook.Worksheets
With Sh
.Rows("18:18").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Range("B18").Value = "FEVEREIRO 18"
.Range("B18").Copy .Range("K18")
.Range("E17:G17").AutoFill Destination:=.Range("E17:G18"), Type:=xlFillDefault
.Range("N17:P17").AutoFill Destination:=.Range("N17:P18"), Type:=xlFillDefault
.Range("C19").FormulaR1C1 = "=SUM(R[-14]C:R[-1]C)"
.Range("C19").Copy .Range("D19")
.Range("C19").Copy .Range("L19")
.Range("C19").Copy .Range("M19")
End With
Next Sh
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by StephenR.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create a macro to round total based on a cell value |
| How to input a row variable pertaining to all macros |
| How to create a macro to copy and paste in the next empty row |
| How to use a macro to auto delete file when passed 15 days |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.