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.