Excel Macro for multiple sheet ??

  • Thread starter Thread starter somu
  • Start date Start date
S

somu

Hai Friends,

I have created a macro(to hide some rows). It is
attached to a command button in sheet1. It is working fine.

Now I want to run the same macro in sheet2 aslo
without opening sheet2. Is there a solution for this ???

Thanks in advance
 
Somu,

It's hard to tell (without seeing the code) whether this will work for you
but replace every cellreferende in your macro from ActiveSheet.Range("A1")
or something like (and offcourse all other cellreferences as well ) to
Worksheets("Sheet2").Range("A1") (In both cases you migth use Cells(1,1)
instead of Range("A1") ).

Better would be (if you always do both sheets together) to use a loop.

For I = 1 to 2
Your macro with Worksheets(I) insttead of something like ActiveWorksheet
or Worksheets("Sheet2")
Next


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Thank u for your reply.

I am sending my code for your reference so that you can tell the exac
solution

this macro is to copy the data from two columns and paste it is colum
"O" and hide all rows which are not having data (CHECK COLUM "O".


Sub HideRows()
Dim rgHide As Range, rw As Range
Dim RST As String ' Reset value
Dim OP As Integer 'Output value
Dim RST_COL
Dim OP_COL


'--------------- FOR unchideing all the rows
Range("A1:A200").Select
Selection.EntireRow.Hidden = False
'--------------------------


RST_COL = Range("AW5").Cells.Value 'take i/p values for reset
OP_COL = Range("AW8").Cells.Value 'take i/p value for o/p
''''''''''''''''''''''''''''''''''


''''''''''''''''''''''
'CLEAR CELLS
Dim i
For i = 4 To No_Row
If (Not Cells(i, 15).Value) Then
Cells(i, 15).Value = ""
End If
'Cells(i, 1).Value = ""
Next i
''''''''''''''''

'copy data from reset to G

For i = 4 To No_Row
If Not Cells(i, RST_COL).Value Then
Cells(i, 15).Value = Cells(i, RST_COL).Value
End If
'Cells(i, 1).Value = ""
Next i


'copy data from O/P to G

For i = 4 To No_Row
If (Cells(i, 15).Value = "") And (Not Cells(i, OP_COL).Value) Then

Cells(i, 15).Value = Cells(i, OP_COL).Value
End If

'Cells(i, 1).Value = ""
Next i



'HIDE CELLS
For Each rw In Range("O4:O200").Rows
If Application.CountA(rw) = 0 Then
If rgHide Is Nothing Then
Set rgHide = rw
Else
Set rgHide = Union(rgHide, rw)
End If
End If
Next rw
If Not rgHide Is Nothing Then rgHide.EntireRow.Hidden = True


End Su
 
Somu,

As far as I see in your code there is nothing in it what forces it to run on
sheet1 only.
You can do two things
Which of the two you have (and want) to do is dependent of what you want.
A) You want to run the code on both sheets by pressing once on the button on
sheet1.
B) You want to input somehow on what sheet the macro has to run if you press
the button on 1.

In the first case (A) :
1) Get the Dim i statement after 'CLEAR CELLS away (and put it direct after
the DIM statement at the to of your program; at the same time change it to
Dim i as Integer).

2) Then Add a statement (in the same area) Dim Sheetnr as Integer.

3) In front of your line : '--------------- FOR unchideing all the rows
add a statement :
For Sheetnr = 1 to 2

4) After your line : If Not rgHide Is Nothing Then rgHide.EntireRow.Hidden =
True add a statement :
Next Sheetnr (or just Next if you like that better)

5) In all you statements (except the comments) between the two mentioned
above, replace Cells(.....) with Worksheets(Sheetnr).Cells(....) and :
Range(....) with Worksheets(Sheetnr).Range(.....)

This solution assumes that Sheet1 and Sheet2 are the 2 first sheets of your
workbook. If not you will have within the For Next loop the value of
Sheetnr and on the basis of give some variable (for instance) SheetName the
value "Sheet1" or "Sheet2" and do as far as point 5 is concerned as in point
3 from the second solution (see next )

In the second case :
1) As 1) above
2) Add a statement Dim Sheetname and load it somehow (for instance with
inputbox) with "Sheet1" or "Sheet2"
3) As 5 above, however now Worksheets(SheetName).Cells(....) or
Worksheets(SheetName).Range(....)


By the way : For both solutions you can replace
Range("A1:A200").Select
Selection.EntireRow.Hidden = False
with
Range("A1:A200").EntireRow.Hidden = False

You don't have to select (or activate) a sheet or a range in order to
perform actions on it. If you avoid selections your code speeds up as well
..
Basicly that's what you are doing with both solutions above as well. You
perform actions on sheet2 without selecting or activating it.

To speed thing further up : Take a look (after everything is working well)
in the help file for Application.Screenupdating as well.

Succes.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top