Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to switch to a different sheet in a macro?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to switch to a different sheet in a macro?
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 -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=378323 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to switch to a different sheet in a macro?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to switch to a different sheet in a macro?
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to switch to a different sheet in a macro?
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 -- BigDav ----------------------------------------------------------------------- BigDave's Profile: http://www.excelforum.com/member.php...nfo&userid=774 View this thread: http://www.excelforum.com/showthread.php?threadid=37832 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to switch to a different sheet in a macro?
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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to switch to a different sheet in a macro?
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 ------------------------------------------------------------------------ BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741 View this thread: http://www.excelforum.com/showthread...hreadid=378323 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to switch to a different sheet in a macro?
Sorry guys, I figure it out. Thanks for all the help -- BigDav ----------------------------------------------------------------------- BigDave's Profile: http://www.excelforum.com/member.php...nfo&userid=774 View this thread: http://www.excelforum.com/showthread.php?threadid=37832 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Switch lookup function to another sheet | Excel Worksheet Functions | |||
Macro "on off" switch | Excel Discussion (Misc queries) | |||
Excel: Arrows scroll sheet instead of move cursor. How to switch | Excel Discussion (Misc queries) | |||
Macro switch between windows question | Excel Discussion (Misc queries) | |||
Macro : switch between excel and word | Excel Programming |