Procedure not working, and I do not know why

[Sibilia]

Hi all,

I have a worksheet call X (list of scenario) shown on the screen, and
have a VBA code which should work on another sheet without showing it o
the screen.

my code is:

Sub DeleteScenario()

Dim j As Integer
Dim k As Integer
Dim count As Integer
Dim SavedScenario1
Dim SavedScenario2

SavedScenario1 = Cells(4, "AB").Value

' sheet "list scenario" activated
With Worksheets("Scenario")
j = 3
Do
j = j + 1

SavedScenario2 = .Cells(j, "A")
Loop Until SavedScenario2 = SavedScenario1
.Rows(j).ClearContents
.Range(Cells(j + 1, "A"), Cells(20, "AU")).Copy

* This is where the procedure bug, and I do not understand why*

.Range(j, "A").PasteSpecial Paste:=xlPasteValues
.Range("A20:AU50").ClearContents
End With
End Sub

Many thanks for your help

Sibili

 

[Guest]

..Range(Cells(j + 1, "A"), Cells(20, "AU")).Copy

should be

..Range(Cells(j + 1, "A"):Cells(20, "AU")).Copy (Replace , with

Please rate this post below

Thanx
BAC

 

[Sibilia]

Hi, the problem cannot be the " ," rather than ":" as I am using cells
object. it is the way of writting it, it is why I do not understand why
it does not work for this specific procedure where it works in an
other.
I think that may be due to the fact that I am doing copy-paste in a
sheet which is not show on the screen;

Any other possible solution ?

Many thanx

Sibilia

 

[Norman Jones]

Hi Sibilia,

Try the following amended version of your code (my arrowed comments follow
the code):

Sub DeleteScenario()

Dim j As Integer
Dim k As Integer
Dim count As Integer
Dim SavedScenario1 As String '<<<
#1
Dim SavedScenario2 As String '<<<
#2

' sheet "list scenario" activated
'<<< #3
With Worksheets("Scenario")
SavedScenario1 = .Cells(4, "AB").Value '<<< #4

j = 3
Do
j = j + 1

SavedScenario2 = .Cells(j, "A")
Loop Until SavedScenario2 = SavedScenario1
.Rows(j).ClearContent
'<<< #5
.Range(.Cells(j + 1, "A"), .Cells(20, "AU")).Copy '<<< #6

'* This is where the procedure bug, and I do not understand why*

.Cells(j, "A").PasteSpecial Paste:=xlPasteValues '<<<
#7
.Range("A20:AU50").ClearContents '<<<
#8
End With
End Sub


#1 It is always advisable to explicitly dim all variables. If the
Saved Scenario variable is not a string, amend.
#2 Ditto
#3 This comment is wrong (or, at least, misleading); the With ... End
With
construct does not *activate* any sheet.
#4 I have moved this line into the With...End With clause and prepended
it with a ,(dot) to ensure that it refers to the Scenario sheet. As
originally
written, it would refer to the active sheet which, you indicate, is
not
the Scenario sheet.
#5 Rows has been prepended with a . dot - see #4
#6 Range and each instance of Cells have been prepended with . (dot) -
see #4
#7 Cells has been prepended with a . (dot) - see #4
#8 Range has been prepended with a . (dot) - see #4

 

[Sibilia]

Hi Norman

THANKS a lot - it works and the message was very clear, with comment
etc.....
Very much appreciated!