Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting worksheets
How can I name the activeworsheet in the beginning of a macro so I can then get back to that worksheet from another sheet later in the macro? I am constantly adding sheets to this workbook, so just telling the macro to select sheet2 just won't work. I've tried Setting the sheet, saying: "Set mysheet = ActiveSheet" and then trying to select mysheet later in the macro (using "Sheets(mysheet).Select") when I want to get back to that sheet, but I keep getting a runtime error 13, type mismatch. When I debug, it brings me to the code were I am trying to get back to that worksheet. I'm still learning this, and taking quite a few lumps. I've pretty much got the macro running beautifully, except for the fact that I keep needing to re-select the sheet I want to work within. It's a bit clumsy, and I'd like to try to make it run a bit smoother. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting worksheets
you need mysheet.Select But I don't recommend using the select method. you don't have to select a worksheet or cells. Set Sht1 = sheets("Sheet1") Set Sht2 = sheets("Sheet2") Set MyRange = Sht1.Range("A1:B10") MyRange.Copy destination:=Sht.Range("C4") "Jacky D." wrote: How can I name the activeworsheet in the beginning of a macro so I can then get back to that worksheet from another sheet later in the macro? I am constantly adding sheets to this workbook, so just telling the macro to select sheet2 just won't work. I've tried Setting the sheet, saying: "Set mysheet = ActiveSheet" and then trying to select mysheet later in the macro (using "Sheets(mysheet).Select") when I want to get back to that sheet, but I keep getting a runtime error 13, type mismatch. When I debug, it brings me to the code were I am trying to get back to that worksheet. I'm still learning this, and taking quite a few lumps. I've pretty much got the macro running beautifully, except for the fact that I keep needing to re-select the sheet I want to work within. It's a bit clumsy, and I'd like to try to make it run a bit smoother. Any help would be appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting worksheets
Don, Here is the code. I know it is pretty ugly, the suggestion that Joel made works, but I know there is a whole lot in this that is unelegant, but I am trying to learn. I'm not sure how the bit of code Joel suggested would fit into this, at this point, I have 68 worksheets in this workbook, and add more daily. I am trying to use this macro to cut down a bit on the boring repetitive stuff I do plus to get the worksheets ready to be printed, after I vaildate the information contained within. I know there is a lot in here that needs to be cleaned up, but I'm learning, and don't do this for a living, just trying to make my job a little easier and learn a bit along the way. ' Copy_from_balance_for_Validation_RAMS Macro Set mysheet = ActiveSheet 'unhide columns Columns("A:CM").Select Selection.EntireColumn.Hidden = False Application.CutCopyMode = False Range("CL29").Activate 'Select tally row cells to copy Dim varcell As Range Set varcell = Application.InputBox("Select rightmost cell in Tally Total Column", _ "Copy Tally Row-Select Cell", Left:=-1, Type:=8) Range(varcell, varcell.End(xlToLeft)).Select Selection.Copy Cells.Select Selection.EntireRow.Hidden = False Range("cl19").Select Sheets("Validation-RAMS").Select Range("B8").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Application.CutCopyMode = False 'delete empty columns in validation sheet For Each cell In Range("B8:B127").Cells If IsEmpty(cell) Or (cell) < 1 And Int(cell.Row) = cell.Row Then cell.Rows.EntireRow.Hidden = True Else cell.Rows.EntireRow.Hidden = False End If Next cell 'copy active values to other rows Range("a8").CurrentRegion.Copy Range("f8").Select ActiveSheet.Paste Range("k8").Select ActiveSheet.Paste Application.CutCopyMode = False Range("a2").Activate MsgBox "Select balance worksheet and total tally range of cells to mget rid of empty values" mysheet.Select Call Balance_Sheet_Clear_Empty_Columns Call Balance_Sheet_Clear_Empty_Rows Sheets("Validation-RAMS").Select End Sub "Don Guillett" wrote: As ALWAYS, post your code for comments and suggestions. -- Don Guillett Microsoft MVP Excel SalesAid Software "Jacky D." wrote in message ... How can I name the activeworsheet in the beginning of a macro so I can then get back to that worksheet from another sheet later in the macro? I am constantly adding sheets to this workbook, so just telling the macro to select sheet2 just won't work. I've tried Setting the sheet, saying: "Set mysheet = ActiveSheet" and then trying to select mysheet later in the macro (using "Sheets(mysheet).Select") when I want to get back to that sheet, but I keep getting a runtime error 13, type mismatch. When I debug, it brings me to the code were I am trying to get back to that worksheet. I'm still learning this, and taking quite a few lumps. I've pretty much got the macro running beautifully, except for the fact that I keep needing to re-select the sheet I want to work within. It's a bit clumsy, and I'd like to try to make it run a bit smoother. Any help would be appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting worksheets
If you want to go back to the sheet use Application.Goto.
Application.Goto mySheet.Range("A1"), Scroll:=True Note this will set focus on A1 in the worksheet you set a reference to here. Set mySheet = ActiveSheet On Jun 24, 4:21*pm, Jacky D. wrote: Don, * Here is the code. I know it is pretty ugly, the suggestion that Joel made works, but I know there is a whole lot in this that is unelegant, but I am trying to learn. I'm not sure how the bit of code Joel suggested would fit into this, at this point, I have 68 worksheets in this workbook, and add more daily. I am trying to use this macro to cut down a bit on the boring repetitive stuff I do plus to get the worksheets ready to be printed, after I vaildate the information contained within. I know there is a lot in here that needs to be cleaned up, but I'm learning, and don't do this for a living, just trying to make my job a little easier and learn a bit along the way. ' Copy_from_balance_for_Validation_RAMS Macro Set mysheet = ActiveSheet 'unhide columns * * Columns("A:CM").Select * * Selection.EntireColumn.Hidden = False * * Application.CutCopyMode = False * * Range("CL29").Activate 'Select tally row cells to copy * * Dim varcell As Range * * Set varcell = Application.InputBox("Select rightmost cell in Tally Total Column", _ * * "Copy Tally Row-Select Cell", Left:=-1, Type:=8) * * * *Range(varcell, varcell.End(xlToLeft)).Select * * * * Selection.Copy * * * * * * Cells.Select * * Selection.EntireRow.Hidden = False * * Range("cl19").Select * * Sheets("Validation-RAMS").Select * * Range("B8").Select * * Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ * * * * False, Transpose:=True * * Application.CutCopyMode = False 'delete empty columns in validation sheet * * For Each cell In Range("B8:B127").Cells * * If IsEmpty(cell) Or (cell) < 1 And Int(cell.Row) = cell.Row Then * * * * cell.Rows.EntireRow.Hidden = True * * Else * * * * cell.Rows.EntireRow.Hidden = False * * End If * * Next cell 'copy active values to other rows * * Range("a8").CurrentRegion.Copy * * Range("f8").Select * * ActiveSheet.Paste * * Range("k8").Select * * ActiveSheet.Paste * * Application.CutCopyMode = False * * *Range("a2").Activate MsgBox "Select balance worksheet and total tally range of cells to mget rid of empty values" mysheet.Select Call Balance_Sheet_Clear_Empty_Columns Call Balance_Sheet_Clear_Empty_Rows Sheets("Validation-RAMS").Select End Sub "Don Guillett" wrote: As ALWAYS, post your code for comments and suggestions. -- Don Guillett Microsoft MVP Excel SalesAid Software "Jacky D." wrote in message ... How can I name the activeworsheet in the beginning of a macro so I can then get back to that worksheet from another sheet later in the macro? I am constantly adding sheets to this workbook, so just telling the macro to select sheet2 just won't work. I've tried Setting the sheet, saying: "Set mysheet = ActiveSheet" and then trying to select mysheet later in the macro (using "Sheets(mysheet).Select") when I want to get back to that sheet, but I keep getting a runtime error 13, type mismatch. When I debug, it brings me to the code were I am trying to get back to that worksheet. I'm still learning this, and taking quite a few lumps. I've pretty much got the macro running beautifully, except for the fact that I keep needing to re-select the sheet I want to work within. It's a bit clumsy, and I'd like to try to make it run a bit smoother. Any help would be appreciated.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting worksheets
I cleaned up part of it a bit. Idea is to NOT select unless necessary. ' Copy_from_balance_for_Validation_RAMS Macro Set mysheet = ActiveSheet 'unhide columns Columns("A:CM").Hidden = False 'Select tally row cells to copy Dim varcell As Range Set varcell = Application. _ InputBox("Select rightmost cell in Tally Total Column", _ "Copy Tally Row-Select Cell", Left:=-1, Type:=8) Range(varcell, varcell.End(xlToLeft)).Copy rows.Hidden = False Sheets("Validation-RAMS").Select Range("B8").PasteSpecial Paste:=xlValues Application.CutCopyMode = False 'delete empty columns in validation sheet For Each cell In Range("B8:B127").Cells If IsEmpty(cell) Or (cell) < 1 And Int(cell.Row) = cell.Row Then cell.Rows.EntireRow.Hidden = True Else cell.Rows.EntireRow.Hidden = False End If Next cell 'copy active values to other rows Range("a8").CurrentRegion.Copy Range("f8") ???? Range("k8").Select ActiveSheet.Paste Application.CutCopyMode = False Range("a2").Activate MsgBox "Select balance worksheet and total tally range of cells to mget rid of empty values" mysheet.Select Call Balance_Sheet_Clear_Empty_Columns Call Balance_Sheet_Clear_Empty_Rows Sheets("Validation-RAMS").Select End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Jacky D." wrote in message ... Don, Here is the code. I know it is pretty ugly, the suggestion that Joel made works, but I know there is a whole lot in this that is unelegant, but I am trying to learn. I'm not sure how the bit of code Joel suggested would fit into this, at this point, I have 68 worksheets in this workbook, and add more daily. I am trying to use this macro to cut down a bit on the boring repetitive stuff I do plus to get the worksheets ready to be printed, after I vaildate the information contained within. I know there is a lot in here that needs to be cleaned up, but I'm learning, and don't do this for a living, just trying to make my job a little easier and learn a bit along the way. ' Copy_from_balance_for_Validation_RAMS Macro Set mysheet = ActiveSheet 'unhide columns Columns("A:CM").Select Selection.EntireColumn.Hidden = False Application.CutCopyMode = False Range("CL29").Activate 'Select tally row cells to copy Dim varcell As Range Set varcell = Application.InputBox("Select rightmost cell in Tally Total Column", _ "Copy Tally Row-Select Cell", Left:=-1, Type:=8) Range(varcell, varcell.End(xlToLeft)).Select Selection.Copy Cells.Select Selection.EntireRow.Hidden = False Range("cl19").Select Sheets("Validation-RAMS").Select Range("B8").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Application.CutCopyMode = False 'delete empty columns in validation sheet For Each cell In Range("B8:B127").Cells If IsEmpty(cell) Or (cell) < 1 And Int(cell.Row) = cell.Row Then cell.Rows.EntireRow.Hidden = True Else cell.Rows.EntireRow.Hidden = False End If Next cell 'copy active values to other rows Range("a8").CurrentRegion.Copy Range("f8").Select ActiveSheet.Paste Range("k8").Select ActiveSheet.Paste Application.CutCopyMode = False Range("a2").Activate MsgBox "Select balance worksheet and total tally range of cells to mget rid of empty values" mysheet.Select Call Balance_Sheet_Clear_Empty_Columns Call Balance_Sheet_Clear_Empty_Rows Sheets("Validation-RAMS").Select End Sub "Don Guillett" wrote: As ALWAYS, post your code for comments and suggestions. -- Don Guillett Microsoft MVP Excel SalesAid Software "Jacky D." wrote in message ... How can I name the activeworsheet in the beginning of a macro so I can then get back to that worksheet from another sheet later in the macro? I am constantly adding sheets to this workbook, so just telling the macro to select sheet2 just won't work. I've tried Setting the sheet, saying: "Set mysheet = ActiveSheet" and then trying to select mysheet later in the macro (using "Sheets(mysheet).Select") when I want to get back to that sheet, but I keep getting a runtime error 13, type mismatch. When I debug, it brings me to the code were I am trying to get back to that worksheet. I'm still learning this, and taking quite a few lumps. I've pretty much got the macro running beautifully, except for the fact that I keep needing to re-select the sheet I want to work within. It's a bit clumsy, and I'd like to try to make it run a bit smoother. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting different Worksheets from a Loop | Excel Programming | |||
Selecting across worksheets | Excel Discussion (Misc queries) | |||
Selecting worksheets into groups? | Excel Programming | |||
Selecting WorkSheets | Excel Programming | |||
selecting worksheets | Excel Programming |