Trying to switch to a different sheet in a macro?

[BigDave]

Here's the code I'm dealing with:

Dim varAnswer As String

varAnswer = MsgBox("This cannot be undone." & Chr(10) & Chr(10) &
"Edits to this workbook my only be entered into your Data Sheet
manually once the current data is compiled.", vbOKCancel)
If varAnswer = vbCancel Then
Exit Sub
End If
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

WITH WORKSHEETS(\"SHEET1\").ACTIVATE
END WITH

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 2
EndRow = 21
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "a").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value <= " " Then .Rows(Lrow).Delete
'This will delete the row if the cell is empty

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

Range("A2:m21").Select
Selection.copy

With Worksheets("sheet2").Activate
End With

End Sub

The bold text is what is the problem. This macro is tied to a
commandbutton on sheet 2. The code gets throught the first message box
and then throws a Type9 error.

I want the macro to work on the data in Sheet1, but I'm stumped.
Thoughts?

 

[davidm]

Bigdave, you got choked by the profusion of WITH ...END constructs. You
ceratainly had one too many.

Trim down

*With Worksheets("Sheet1").Activate
End With*

to

Worksheets("Sheet1").Activate

 

[Guest]

Does Sheet1 have to be the active sheet? Try your code without activating
Sheet1.

Using the CodeName for Sheet1:

With Sheet1
.DisplayPageBreaks = False
<your code>
end with


Using the Name for Sheet1 (the name that appears on the tab)

With Worksheets("Sheet1")
.DisplayPageBreaks = False
<your code>
end with


If you do want/need to activate sheet1

Using CodeName:
Sheet1.Activate

Using Name:
Worksheets("Sheet1").Activate

 

[STEVE BELL]

If you just want to activate the sheet:

Sheets("Sheet1").Activate
or
Sheets("Sheet1").Select

If you just want to work on Sheet1 without activating:

With Sheets("Sheet1")
.Range("A1").Text = "Hello!"
.Range("C2:E5").ClearContents
End With

or you could write code like:
Sheets("Sheet1").Range("A1").Text = "Hello!"
Sheets("Sheet1").Range("C2:E5").ClearContents

 

[BigDave]

Just so that I am clear on what I'm trying to accompish.

The code in my original post was written to be run on the same shee
where the button was. I want the code to be behave the same way, d
the very same thing, on the same sheet, but I want the button tha
initiates the code on a different sheet.

Here is the original code:

Private Sub CommandButton1_Click()
Dim varAnswer As String

varAnswer = MsgBox("This cannot be undone." & Chr(10) & Chr(10)
"Edits to this workbook my only be entered into your Data Shee
manually once the current data is compiled.", vbOKCancel)
If varAnswer = vbCancel Then
Exit Sub
End If
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 2
EndRow = 21
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "a").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "A").Value <= " " Then .Rows(Lrow).Delete
'This will delete the row if the cell is empty

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

Range("A2:m21").Select
Selection.copy

End Sub

If it helps, what the code does is look for the rows with blanks i
Column A and deletes those starting and ending where specified

 

[Dick Kusleika]

Here's the code I'm dealing with:

Not what you asked, but...

Dim lngAnswer as Long

lngAnswer = MsgBox(etc...)

Your variable's prefix makes it look like a Variant data type (although it
may mean variable), is dimmed as a String data type, and MsgBox returns a
Long Integer. Consider shoring all that up as above.

 

[BigDave]

Dick - thanks but the code is performing just as I need it to.

I'm still can't get the code to excecute on the cells on sheet 1 from a
button on sheet 2.

 

[BigDave]

Sorry guys, I figure it out. Thanks for all the help