PDA

View Full Version : Solved: Loop just in activesheet rather than all worksheets



AnswerSeeker
03-29-2012, 12:18 AM
Hello,

I want that my procedures (inserting 2 additional lines at the top in color) are done for every worksheet. But rather than it is only working the activesheet (in my case I have 3 worksheets, and my code insert 6 lines in the activesheet and do nothing in the other worksheets). What is my mistake?

Sub Kombi()

Application.ScreenUpdating = False
For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
numbers = ActiveSheet.UsedRange.Columns.Count
For i = 1 To 2
ActiveSheet.Rows(i).Insert
Next i
For col = 1 To numbers
Cells(1, col).Interior.Color = vbGreen
Cells(2, col).Interior.Color = vbGreen
Next col
End With
Next wkSht
Application.ScreenUpdating = True
End Sub

p45cal
03-29-2012, 01:26 AM
remove every occurence of
Activesheet
(but leave the dot after it).

p45cal
03-29-2012, 02:53 AM
apart from my misspelling ocurrence, the macro would not colour any cells except in the active sheet, so the updated macro would be:
Sub Kombi()
Application.ScreenUpdating = False
For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
numbers = .UsedRange.Columns.Count
For i = 1 To 2
.Rows(i).Insert
Next i
For col = 1 To numbers
.Cells(1, col).Interior.Color = vbGreen
.Cells(2, col).Interior.Color = vbGreen
Next col
End With
Next wkSht
Application.ScreenUpdating = True
End Sub
but even that has an unnecessasry loop in and could be:
Sub Kombi()
Application.ScreenUpdating = False
For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
numbers = .UsedRange.Columns.Count
For i = 1 To 2
.Rows(i).Insert
Next i
.Cells(1, 1).Resize(2, numbers).Interior.Color = vbGreen
End With
Next wkSht
Application.ScreenUpdating = True
End Sub
..or a bit shorter still:
Sub Kombi2()
For Each wksht In ActiveWorkbook.Worksheets
Set x = wksht.UsedRange.Rows("1:2")
x.EntireRow.Insert
x.Offset(-2).Interior.Color = vbGreen
Next wksht
End Sub

AnswerSeeker
03-30-2012, 12:01 AM
It is working now, thx.