For Each Code Not Working

  • Thread starter Thread starter jacqui
  • Start date Start date
J

jacqui

I've had a couple of replies to my earlier question but
unfortunately the solutions do not work. The code runs
through the loop and converts my formula to values for the
first worksheet but when it gets to next sht, the code is
not selecting the next sheet in the workbook. In stepping
through I've noted the value of my variable sht =
nothing. Surely I must need to set this to something.
I'm confused. Can anyone help?
My code is below.
Many thanks
Jacqui

Dim iCnt As Integer

Dim sRTitle As String
Dim sht As Worksheet

ActiveWindow.SelectedSheets.Copy

ActiveWindow.Caption = "ERS-Formula to value"
Application.StatusBar = "Converting formula to
values"

iCnt = Worksheets.Count

For Each sht In ActiveWorkbook.Worksheets

EndRow = Range("A65000").End(xlUp).Row + 1
iCCount = Range("IV7").End(xlToLeft).Column

Range("A1", Cells(EndRow, iCCount)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Next sht
 
jacqui said:
I've had a couple of replies to my earlier question but
unfortunately the solutions do not work. The code runs
through the loop and converts my formula to values for the
first worksheet but when it gets to next sht, the code is
not selecting the next sheet in the workbook. In stepping
through I've noted the value of my variable sht =
nothing. Surely I must need to set this to something.
I'm confused. Can anyone help?
My code is below.
Many thanks
Jacqui
Click to expand...

You arent explicitly telling the application
which sheet to select the ranges on so it assumes
you mean the active sheet (the one you ran the macro from)

If you prefix the range with the Sheet object all will be well

i.e.

Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets

with sht
EndRow = .Range("A65000").End(xlUp).Row + 1
iCCount = .Range("IV7").End(xlToLeft).Column
.Range("A1", Cells(EndRow, iCCount)).Select

etc

End With

Next sht
Keith
 
Hi,

I think this may be a case where you need to to activate each sheet.

Regards,
Don Lloyd.
 
I like Keith's suggestion, but I think he missed a dot and you can't select a
range on a sheet that isn't active.

But this seemed to work ok for me:

Option Explicit
Sub testme00()

Dim iCnt As Long
Dim EndRow As Long
Dim iCCount As Long
Dim sRTitle As String
Dim sht As Worksheet

ActiveWindow.SelectedSheets.Copy

ActiveWindow.Caption = "ERS-Formula to value"
Application.StatusBar = "Converting formula to Values"

iCnt = Worksheets.Count 'delete this?
For Each sht In ActiveWorkbook.Worksheets
With sht
EndRow = .Range("A65000").End(xlUp).Row + 1
iCCount = .Range("IV7").End(xlToLeft).Column
With .Range("A1", .Cells(EndRow, iCCount))
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
End With
Next sht

End Sub
 
Back
Top