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

[JMB]

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




"BigDave" wrote:

>
> 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?
>
>
> --
> BigDave
> ------------------------------------------------------------------------
> BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
> View this thread: http://www.excelforum.com/showthread...hreadid=378323
>
>

[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

--
steveB

Remove "AYN" from email to respond
"BigDave" <BigDave.1qha6b_1118520304.3474@excelforum-nospam.com> wrote in
message news:BigDave.1qha6b_1118520304.3474@excelforum-nospam.com...
>
> 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?
>
>
> --
> BigDave
> ------------------------------------------------------------------------
> BigDave's Profile:
> http://www.excelforum.com/member.php...fo&userid=7741
> View this thread: http://www.excelforum.com/showthread...hreadid=378323
>

[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 sheet where the button was. I want the code to be behave the same way, do the very same thing, on the same sheet, but I want the button that 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 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 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 in Column A and deletes those starting and ending where specified.

[Dick Kusleika]

BigDave wrote:
> 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

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.

--
**** Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.*****-blog.com

[BigDave]

**** - 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.