ExcelBanter - Selecting worksheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Selecting worksheets (https://www.excelbanter.com/excel-programming/430296-selecting-worksheets.html)

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.

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.

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.

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 -

Selecting worksheets