Trying to switch to a different sheet in a macro?

  • Thread starter Thread starter BigDave
  • Start date Start date
B

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
Click to expand...
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?
 
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
 
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
 
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
 
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
Click to expand...

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
 
BigDave said:
Here's the code I'm dealing with:
Click to expand...

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.
 
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.
 
Back
Top