Selecting worksheets

[Jacky D.]

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.

 

[Don Guillett]

As ALWAYS, post your code for comments and suggestions.

 

[Joel]

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

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

 

[norie]

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

 

[Don Guillett]

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